Excel VBA Select Case and multiple controls

[cane]

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/YYYY	YYYYMMDD-555	CRA-00001	BBM155	BBM1
MM/DD/YYYY	YYYYMMDD-555	CRA-00001	BBM108	BBM2
MM/DD/YYYY	YYYYMMDD-555	CRA-00001	BBM516	BBM3

<COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><TBODY>
</TBODY>

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/YYYY	YYYYMMDD-555	CRA-00001	3	BBM155	BBM1
MM/DD/YYYY	YYYYMMDD-555	CRA-00001	2	BBM108	BBM2
MM/DD/YYYY	YYYYMMDD-555	CRA-00001	7	BBM516	BBM3

<COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><TBODY>
</TBODY>

I get this:

MM/DD/YYYY	YYYYMMDD-555	CRA-00001	3	BBM155	BBM1
MM/DD/YYYY	YYYYMMDD-555	CRA-00001	3	BBM108	BBM2
MM/DD/YYYY	YYYYMMDD-555	CRA-00001	3	BBM516	BBM3

<COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><TBODY>
</TBODY>

			2
			7

<COLGROUP><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" width=118><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 101pt; mso-width-source: userset; mso-width-alt: 4937" width=135><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 156pt; mso-width-source: userset; mso-width-alt: 7606" width=208><COL style="WIDTH: 40pt; mso-width-source: userset; mso-width-alt: 1938" width=53><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><TBODY>
</TBODY>

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

 

[cane]

This was solved on ozgrid by adding this line of code:

 wsh.Cells(lngRow , 11).Value = Me.Controls("TextBox" & CStr(i + 9)).Value

.

Modify VBA code with select case me.controls