Excel VBA Select Case and multiple controls

C

cane

New Member
Joined
Dec 5, 2012
Messages
13
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:
[TABLE="width: 892"]
<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>[TR]
[TD="class: xl71, width: 118, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl63, width: 208, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl63, width: 135, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl68, width: 81, bgcolor: transparent"]BBM155[/TD]
[TD="class: xl64, width: 208, bgcolor: transparent"]BBM1[/TD]
[/TR]
[TR]
[TD="class: xl71, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl63, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl63, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl68, bgcolor: transparent"]BBM108[/TD]
[TD="class: xl64, bgcolor: transparent"]BBM2[/TD]
[/TR]
[TR]
[TD="class: xl70, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl63, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl63, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl68, bgcolor: transparent"]BBM516[/TD]
[TD="class: xl64, bgcolor: transparent"]BBM3[/TD]
[/TR]
</TBODY>[/TABLE]

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)
[TABLE="width: 892"]
<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>[TR]
[TD="class: xl73, width: 118, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl65, width: 208, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl65, width: 135, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl71, width: 127, bgcolor: transparent"]3[/TD]
[TD="class: xl70, width: 81, bgcolor: transparent"]BBM155[/TD]
[TD="class: xl66, width: 208, bgcolor: transparent"]BBM1[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl65, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl65, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl71, bgcolor: transparent"]2[/TD]
[TD="class: xl70, bgcolor: transparent"]BBM108[/TD]
[TD="class: xl66, bgcolor: transparent"]BBM2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl65, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl65, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl71, bgcolor: transparent"]7[/TD]
[TD="class: xl70, bgcolor: transparent"]BBM516[/TD]
[TD="class: xl66, bgcolor: transparent"]BBM3[/TD]
[/TR]
</TBODY>[/TABLE]


I get this:
[TABLE="width: 892"]
<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>[TR]
[TD="class: xl73, width: 118, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl65, width: 208, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl65, width: 135, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl71, width: 127, bgcolor: transparent"]3[/TD]
[TD="class: xl70, width: 81, bgcolor: transparent"]BBM155[/TD]
[TD="class: xl66, width: 208, bgcolor: transparent"]BBM1[/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl65, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl65, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl71, bgcolor: transparent"]3[/TD]
[TD="class: xl70, bgcolor: transparent"]BBM108[/TD]
[TD="class: xl66, bgcolor: transparent"]BBM2[/TD]
[/TR]
[TR]
[TD="class: xl72, bgcolor: transparent"]MM/DD/YYYY[/TD]
[TD="class: xl65, bgcolor: transparent"]YYYYMMDD-555[/TD]
[TD="class: xl65, bgcolor: transparent"]CRA-00001[/TD]
[TD="class: xl71, bgcolor: transparent"]3[/TD]
[TD="class: xl70, bgcolor: transparent"]BBM516[/TD]
[TD="class: xl66, bgcolor: transparent"]BBM3[/TD]
[/TR]
</TBODY>[/TABLE]
[TABLE="width: 892"]
<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>[TR]
[TD="class: xl73, width: 118, bgcolor: transparent"] [/TD]
[TD="class: xl66, width: 208, bgcolor: transparent"] [/TD]
[TD="class: xl65, width: 135, bgcolor: transparent"] [/TD]
[TD="class: xl72, width: 127, bgcolor: transparent"]2[/TD]
[TD="class: xl71, width: 81, bgcolor: transparent"] [/TD]
[TD="class: xl67, width: 208, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl73, bgcolor: transparent"] [/TD]
[TD="class: xl66, bgcolor: transparent"] [/TD]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[TD="class: xl72, bgcolor: transparent"]7[/TD]
[TD="class: xl71, bgcolor: transparent"] [/TD]
[TD="class: xl67, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
537
smozgur
smozgur
B
  • Solved
Excel Date Format is getting changed after running VBA code
Replies
8
Views
991
6StringJazzer
6StringJazzer
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
T
  • Question Question
VBA to launch userform by selecting a cell
2
Replies
10
Views
2K
Logit
Logit
N
  • Question Question
How to Create Multiple Reports from One Sheet
Replies
1
Views
2K
Logit
Logit

Forum statistics

Threads
1,223,243
Messages
6,170,971
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top