[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.excel.programming

select case statement?

CR

12/20/2006 12:27:00 AM

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 Answers

Steve Karnath

12/20/2006 12:42:00 AM

0

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" <flattracker@harbornet.com> wrote in message
news:jpqdnRTRj7VhHBXYRVnyvwA@harbornet.com...
> 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
>


Martin Fishlock

12/20/2006 1:00:00 AM

0

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
>
>
>

Roger Govier

12/20/2006 1:06:00 AM

0

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" <flattracker@harbornet.com> wrote in message
news:jpqdnRTRj7VhHBXYRVnyvwA@harbornet.com...
> 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
>


PapaDos

12/20/2006 6:53:00 AM

0

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
>
>
>