Cases within Cases

[mtm4300 via OfficeKB.com]

I have two comboxes in my worksheet. The first has 3 selections and each
selection changes the data in the second combobox, which has 7 selections.
Each of the 7 selections will change text within cells. I have the second
combobox setup to change by selecting a case of the first one. Now I need to
change the cells by using second combobox and I am trying to use the Select
Case function. I sit possible to have 'subcases?' Here is an example of the
code I have:

Sub CreateMethodE()

' Creates the three different methods in the English measurement System.

Dim idex As Long
Dim newname As Worksheet
Set newname = Sheets("Program")

On Error Resume Next
Worksheets(1).DropDowns("MethodE").Delete
On Error GoTo 0
On Error Resume Next
Worksheets(1).DropDowns("typeE").Delete
On Error GoTo 0

With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=245, Top:=189.75, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 3
.ControlFormat.AddItem "E1: Mainline or Public Road Approach", 1
.ControlFormat.AddItem "E2: Drive, Including Class V", 2
.ControlFormat.AddItem "E3: Median/Mainline or Public Road Approach*", 3
.Name = "MethodE"
.OnAction = "MethodE_Change"

End With

End Sub
___________________
Sub MethodE_Change()

Dim idex As Long
Dim newname As Worksheet
Set newname = Sheets("Program")

On Error Resume Next
Worksheets(1).DropDowns("typeE").Delete
On Error GoTo 0

idex = Worksheets(1).DropDowns("MethodE").ListIndex
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=245, Top:=309, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 7
.Name = "typeE"

Select Case idex

Case 1

.ControlFormat.AddItem "E1: Circular Corrugated Pipe", 1
.ControlFormat.AddItem "E1: Circular Corrugated Pipe (SPM)", 2
.ControlFormat.AddItem "E1: Circular Smooth-Interior Pipe", 3
.ControlFormat.AddItem "E1: Deformed Corrugated Pipe", 4
.ControlFormat.AddItem "E1: Deformed Corrugated Pipe (SPAA)", 5
.ControlFormat.AddItem "E1: Deformed Corrugated PIpe (SPS)", 6
.ControlFormat.AddItem "E1: Deformed Smooth-Interior Pipe", 7
.OnAction = "E1Pipe1_Change"

I have 2 more cases, but they will be the same as this one. I have a
subroutine called "E1Pipe!_Change" but I dont know what to put in there to
change the cells each time a user clicks on a different option. Any help
would be greatly appreciated!

 

[Tom Ogilvy]

Change B9 to the cell you want to reflect the value of the TypeE combobox.

Public Sub E1Pipe1_Change()
Worksheets("Program").Range("B9").Value = _
Worksheets("Program").DropDowns("("typeE").Value
End sub

 

[mtm4300 via OfficeKB.com]

Whenever I input your code, an error message comes up saying there needs to
be a seperator. Also, after that code do I just start in with the Case 1...or
start with Case "E1: Circular Corrugated Pipe." And also, is there a way just
to clear text and not the whole cell. I have a border around the cell and do
not want to lose it each time the cell changes? Thank you!

Change B9 to the cell you want to reflect the value of the TypeE combobox.

Public Sub E1Pipe1_Change()
Worksheets("Program").Range("B9").Value = _
Worksheets("Program").DropDowns("("typeE").Value
End sub

I have two comboxes in my worksheet. The first has 3 selections and each
selection changes the data in the second combobox, which has 7 selections.

[quoted text clipped - 65 lines]

change the cells each time a user clicks on a different option. Any help
would be greatly appreciated!

 

[Tom Ogilvy]

Guess there is a typo in the code

Public Sub E1Pipe1_Change()
Worksheets("Program").Range("B9").Value = _
Worksheets("Program").DropDowns("typeE").Value
End sub

All it does it put in the value selected in dropdown typeE into cell B9 (as
written).

It is unclear why you would need a case statement within this code.

if the user selects
E1: Mainline or Public Road Approach

from the dropdown, then cell B9 will contain

E1: Mainline or Public Road Approach

If you want something else, then you need to explain what you want.

It will only change the value of the cell. It will not affect formatting.

--
Regards,
Tom Ogilvy

Whenever I input your code, an error message comes up saying there needs to
be a seperator. Also, after that code do I just start in with the Case 1...or
start with Case "E1: Circular Corrugated Pipe." And also, is there a way just
to clear text and not the whole cell. I have a border around the cell and do
not want to lose it each time the cell changes? Thank you!

Change B9 to the cell you want to reflect the value of the TypeE combobox.

Public Sub E1Pipe1_Change()
Worksheets("Program").Range("B9").Value = _
Worksheets("Program").DropDowns("("typeE").Value
End sub

I have two comboxes in my worksheet. The first has 3 selections and each
selection changes the data in the second combobox, which has 7

selections.
[quoted text clipped - 65 lines]

change the cells each time a user clicks on a different option. Any help
would be greatly appreciated!

 

[mtm4300 via OfficeKB.com]

If the user selects 'E1 Mainline' (from the first combobox) then the second
combobox appears with 7 selections. The user will select one of the 7 options
in the second combobox. Then a range of criteria will appear in cells. This
criteria will change for each selection out of the 7 choices in the second
combobox. And the second combobox will change pending on the selection from
the first combobox.

Guess there is a typo in the code

Public Sub E1Pipe1_Change()
Worksheets("Program").Range("B9").Value = _
Worksheets("Program").DropDowns("typeE").Value
End sub

All it does it put in the value selected in dropdown typeE into cell B9 (as
written).

It is unclear why you would need a case statement within this code.

if the user selects
E1: Mainline or Public Road Approach

from the dropdown, then cell B9 will contain

E1: Mainline or Public Road Approach

If you want something else, then you need to explain what you want.

It will only change the value of the cell. It will not affect formatting.

Whenever I input your code, an error message comes up saying there needs to
be a seperator. Also, after that code do I just start in with the Case 1...or

[quoted text clipped - 14 lines]

 

[Tom Ogilvy]

If you want to put all your data hard coded into your procedures, you can
certainly do that, but that practice is generally not encouraged as it is
usually harder to change data by editing code than to change it in some form
of data repository and retrieving it.


Public Sub E1Pipe1_Change()
Dim drpdwn as Dropdown
s = Application.Caller
set drpdwn = Worksheets("Program").Dropdowns(s)
Select Case drpdwn.List
Case 1
Range("B9").Value = drpdwn.list(drpdwn.listindex)
Range("B13").Value = .8125
Range("C11").Value = 21
Case 2
Range("B9").Value = drpdwn.list(drpdwn.listindex)
Range("B13").Value = .5
Range("C11").Value = 10
Case 3

Case 4

. . .

Case 7


End Select

End Sub


You can have case statements within case statements

Dim i as Long, j as String, k as String
Select Case i
Case 1
Select Case j
Case "A"

Case "B"

End Select
Case 2
Select Case k
Case "R"

Case "S"


End Select
End Select

--
Regards,
Tom Ogilvy

If the user selects 'E1 Mainline' (from the first combobox) then the second
combobox appears with 7 selections. The user will select one of the 7 options
in the second combobox. Then a range of criteria will appear in cells. This
criteria will change for each selection out of the 7 choices in the second
combobox. And the second combobox will change pending on the selection from
the first combobox.

Guess there is a typo in the code

Public Sub E1Pipe1_Change()
Worksheets("Program").Range("B9").Value = _
Worksheets("Program").DropDowns("typeE").Value
End sub

All it does it put in the value selected in dropdown typeE into cell B9 (as
written).

It is unclear why you would need a case statement within this code.

if the user selects
E1: Mainline or Public Road Approach

from the dropdown, then cell B9 will contain

E1: Mainline or Public Road Approach

If you want something else, then you need to explain what you want.

It will only change the value of the cell. It will not affect formatting.

Whenever I input your code, an error message comes up saying there needs to
be a seperator. Also, after that code do I just start in with the Case

1...or
[quoted text clipped - 14 lines]

change the cells each time a user clicks on a different option. Any help
would be greatly appreciated!

 

[mtm4300 via OfficeKB.com]

Here is what I am trying to do. In the code I have:

Case 1
.ControlFormat.AddItem "E1: Circular Corrugated Pipe", 1
.ControlFormat.AddItem "E1: Circular Corrugated Pipe (SPM)", 2
.....
If some clicks on "E1:Circular Corrugated Pipe" I want text to be placed in 5
cells (K26:K30). If someone clicks on "E1: Circular Corrugated Pipe (SPM)" I
want text to be placed in 3 cells (K26:K28). The two extra cells will be
cleared. On screen, the criteria will appear, and the user will begin to
input his/her data (L26:L30).

If you want to put all your data hard coded into your procedures, you can
certainly do that, but that practice is generally not encouraged as it is
usually harder to change data by editing code than to change it in some form
of data repository and retrieving it.

Public Sub E1Pipe1_Change()
Dim drpdwn as Dropdown
s = Application.Caller
set drpdwn = Worksheets("Program").Dropdowns(s)
Select Case drpdwn.List
Case 1
Range("B9").Value = drpdwn.list(drpdwn.listindex)
Range("B13").Value = .8125
Range("C11").Value = 21
Case 2
Range("B9").Value = drpdwn.list(drpdwn.listindex)
Range("B13").Value = .5
Range("C11").Value = 10
Case 3

Case 4

. . .

Case 7

End Select

End Sub

You can have case statements within case statements

Dim i as Long, j as String, k as String
Select Case i
Case 1
Select Case j
Case "A"

Case "B"

End Select
Case 2
Select Case k
Case "R"

Case "S"

End Select
End Select

If the user selects 'E1 Mainline' (from the first combobox) then the second
combobox appears with 7 selections. The user will select one of the 7 options

[quoted text clipped - 31 lines]

 

[Tom Ogilvy]

You would need to place that code in the code you assign to the onaction
property of combobox2. The code you show is loading combobox2 with
choices. You would also assign an onaction macro at this time - the macro
referred to in the first sentence. The code you show was in the macro
assigned to the onaction property for combobox1.

So combobox1 would have an onaction macro assigned that would load combobox2
with choices and assign the appropriate onaction macro to combobox2

The combobox2 on action macro would contain the case statements to react to
the choice in Combobox2. It would do the filling and clearing of the
appropriate cells based on the choice made.

--
Regards,
Tom Ogilvy

Here is what I am trying to do. In the code I have:

Case 1
.ControlFormat.AddItem "E1: Circular Corrugated Pipe", 1
.ControlFormat.AddItem "E1: Circular Corrugated Pipe (SPM)", 2
.....
If some clicks on "E1:Circular Corrugated Pipe" I want text to be placed in 5
cells (K26:K30). If someone clicks on "E1: Circular Corrugated Pipe (SPM)" I
want text to be placed in 3 cells (K26:K28). The two extra cells will be
cleared. On screen, the criteria will appear, and the user will begin to
input his/her data (L26:L30).

If you want to put all your data hard coded into your procedures, you can
certainly do that, but that practice is generally not encouraged as it is
usually harder to change data by editing code than to change it in some form
of data repository and retrieving it.

Public Sub E1Pipe1_Change()
Dim drpdwn as Dropdown
s = Application.Caller
set drpdwn = Worksheets("Program").Dropdowns(s)
Select Case drpdwn.List
Case 1
Range("B9").Value = drpdwn.list(drpdwn.listindex)
Range("B13").Value = .8125
Range("C11").Value = 21
Case 2
Range("B9").Value = drpdwn.list(drpdwn.listindex)
Range("B13").Value = .5
Range("C11").Value = 10
Case 3

Case 4

. . .

Case 7

End Select

End Sub

You can have case statements within case statements

Dim i as Long, j as String, k as String
Select Case i
Case 1
Select Case j
Case "A"

Case "B"

End Select
Case 2
Select Case k
Case "R"

Case "S"

End Select
End Select

If the user selects 'E1 Mainline' (from the first combobox) then the second
combobox appears with 7 selections. The user will select one of the 7

options
[quoted text clipped - 31 lines]

change the cells each time a user clicks on a different option. Any help
would be greatly appreciated!