Unsure how to change vba code to work properly select case and me.controls

cane

New member
Joined
May 21, 2013
Messages
2
Reaction score
0
Points
0
I have this code: It works as displayed in the first table.
Set wbk = Workbooks("News.xlsm")
For i = 2 To 11
Select Case Me.Controls("Combobox" & i).Value
Case "BBM155", "BBM108", "BBM516"
strSheet = "BBM"
Case "FITH155", "FITH108", "FITH516"
strSheet = "FITH"
Case "AGL155", "AGL108", "AGL516"
strSheet = "Agls"
Case "DMN155", "DMN108", "DMN516"
strSheet = "Dmns"
Case "BMC155", "BMC108", "BMC516"
strSheet = "BMC"

Case Else
strSheet = ""
End Select

If strSheet <> "" Then
Set wsh = wbk.Worksheets(strSheet)
Set rngCell = wsh.Range("g" & wsh.Columns.Count).End(xlUp).Offset(0, 1)
If rngCell Is Nothing Then
lngRow = 9
Else
lngRow = rngCell.Row + 1

End If
' Substitute the correct names of the controls
wsh.Cells(lngRow, 2).Value = Me.TextBox1.Value 'Date
wsh.Cells(lngRow, 3).Value = Me.TextBox3.Value 'Invoice #
wsh.Cells(lngRow, 4).Value = Me.ComboBox12.Value 'Customer account #
wsh.Cells(lngRow, 7).Value = Me.Controls("ComboBox" & i).Value 'Items 1-10
End If
Next i

This is what the above code does right now:
MM/DD/YYYYYYYYMMDD-555CRA-00001BBM155BBM1
MM/DD/YYYYYYYYMMDD-555CRA-00001BBM108BBM2
MM/DD/YYYYYYYYMMDD-555CRA-00001BBM516BBM3


I want to add the below to capture a numeric value associated with each entry. However when I just add the below code to the above it does not work as I expected it would.

wsh.Cells(lngRow, 6).Value = Me.TextBox11.Value 'this one works, but applies to all items
wsh.Cells(lngRow + 1, 6).Value = Me.TextBox12.Value 'Total number of this item ordered
wsh.Cells(lngRow + 2, 6).Value = Me.TextBox13.Value 'Total number of this item ordered
wsh.Cells(lngRow + 3, 6).Value = Me.TextBox14.Value 'Total number of this item ordered
wsh.Cells(lngRow + 4, 6).Value = Me.TextBox15.Value 'Total number of this item ordered
wsh.Cells(lngRow + 5, 6).Value = Me.TextBox16.Value 'Total number of this item ordered
wsh.Cells(lngRow + 6, 6).Value = Me.TextBox17.Value 'Total number of this item ordered
wsh.Cells(lngRow + 7, 6).Value = Me.TextBox18.Value 'Total number of this item ordered
wsh.Cells(lngRow + 8, 6).Value = Me.TextBox19.Value 'Total number of this item ordered
wsh.Cells(lngRow + 9, 6).Value = Me.TextBox20.Value 'Total number of this item ordered

Instead of this: Wher I have a numeric value associated with that order(Column four here)
MM/DD/YYYYYYYYMMDD-555CRA-000013BBM155BBM1
MM/DD/YYYYYYYYMMDD-555CRA-000012BBM108BBM2
MM/DD/YYYYYYYYMMDD-555CRA-000017BBM516BBM3



I get this:
MM/DD/YYYYYYYYMMDD-555CRA-000013BBM155BBM1
MM/DD/YYYYYYYYMMDD-555CRA-000013BBM108BBM2
MM/DD/YYYYYYYYMMDD-555CRA-000013BBM516BBM3

2
7


The code repeats the first number for all entries then lists out the additional numbers in blank rows. I've tried to add an additional control, and more if then statements but I am very weak in VBA. Can anyone help?

Thanks,
Cane​
 

JoePublic

Super Moderator
Staff member
Joined
Sep 16, 2011
Messages
220
Reaction score
0
Points
16
Location
UK
Excel Version(s)
2016
Crossposted (and in progress) here.
 
Top