Hi

Since there will be only 1 result from the Combo box, rather than 16
Case statements, I might be inclined to set up a table with the ranges
belonging to different Wk numbers
Wk1 B3:C18
Wk2 G3:H18 etc.

then use something like

Sub ComboBox2_Change()
Dim rng As Range, test as string, test2 as string
'changes week reference number
'B20 stores the last wk viewed
Sheets("Schedule").Range("B20").Select
'ComboBox2.Value = the wk that is shown on form
test = ComboBox2.Value
test2 = Application.WorksheetFunction.VLookup(test,
Sheets("Sheet1").Range("A1:B16"), 2, 0)
Set rng = Sheets("Schedule").Range(test2)
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MSForms.Label Then
j = j + 1
ctrl.Caption = rng(j).Value
End If
Next
End Sub

having changed the Sheets("Sheet1").Range("A1:B16") to the relevant
location of my table

--
Regards

Roger Govier


"CR" wrote in message
...
Hi, I have the following code behind a userform that works, but I
don't think that it is the way to go with it. I have not gone any
further than this on the code. The labels on the form will have to
populate from one of 16 different ranges, week one is B3:C18, wk 2
G3:H18 ect. depending on the value in Combobox2.

I have never used a select case statement, but if I understand it, I
think it will be better than doing with more IF's and multiple subs. I
already see that I could get rid of the extra subs by changing the
range with IF's and combining the ComboBox2_Change sub and one of the
others but it would still be a lot of IF's

Can someone give me a short snippet that would put me in the right
direction or correct me if I am wrong about the select case being
better.

Thanks
CR

Sub ComboBox2_Change()
'changes week reference number
'B20 stores the last wk viewed
Sheets("Schedule").Range("B20").Select
'ComboBox2.Value = the wk that is shown on form
ActiveCell.Value = ComboBox2.Value
If ComboBox2.Value = "WK1" Then
Rd_Wk1
Else
If ComboBox2.Value = "WK2" Then
Rd_Wk2
End If
End If

End Sub

Sub Rd_Wk1()
'Reads wk 1 schedule
Set rng = Range("B3:C18")
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MSForms.Label Then
j = j + 1
ctrl.Caption = rng(j).Value
End If
Next
End Sub

Sub Rd_Wk2()
'Reads wk 2 schedule
Set rng = Range("G3:H18")
For Each ctrl In UserForm1.Controls
If TypeOf ctrl Is MSForms.Label Then
j = j + 1
ctrl.Caption = rng(j).Value
End If
Next
End Sub