Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case statement?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case statement?
You might try
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 Select Case ActiveCell.Value Case "WK1" Set rng = Range("B3:C18") Case "WK2" Set rng = Range("G3:H18") End Select For Each ctrl In UserForm1.Controls If TypeOf ctrl Is MSForms.Label Then j = j + 1 ctrl.Caption = rng(j).Value End If Next "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case statement?
CR put the range references into an array and then use the array:
see the code szWeekRanges = array(""B3:C18", "G3:H18" ) ' add more here and add more ranges here for the weeks. option base 1 Sub ComboBox2_Change() dim iWeekNr as integer dim szWeekRanges as variant szWeekRanges = array(""B3:C18", "G3:H18" ) ' add more here 'changes week reference number 'B20 stores the last wk viewed iWeekNr = ComboBox2.Value Sheets("Schedule").Range("B20") = iWeekNr If iWeekNr = LBound(szWeekRanges) and _ iWeekNr <= UBound(szWeekRanges) then Rd_Wk(szWeekRanges(iWeekNr)) Else msgbox "Error occured deal with it....." End If End Sub Sub Rd_Wk(szRgn as string) Set rng = Range(szRgn) 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 -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "CR" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case statement?
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case statement?
Why don't you name your ranges according to the options showing in your
comboBox ? Set rng = Range(ComboBox2.Value) -- Regards, Luc. "Festina Lente" "CR" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
select case statement?
Thank you all. Seems there is more than one way to skin a cat. Since I am on
the bottom of a steep learning curve in Excel VBA, I think I will play with all of the suggestions just to try and get an understanding of each. Thanks again. CR "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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select Case Statement | Excel Worksheet Functions | |||
Select Case within IF-Else statement | Excel Programming | |||
select case statement | Excel Programming | |||
Select Case Statement does not evaluate | Excel Programming |