Cases within Cases

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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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

--
Regards,
Tom Ogilvy

"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5bf80b2736414@uwe...
> 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!
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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!


Tom Ogilvy wrote:
>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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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



"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5bf87d4693290@uwe...
> 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!
>
>
> Tom Ogilvy wrote:
> >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!
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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.

Tom Ogilvy wrote:
>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!

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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


"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5bf8bc9faf9d4@uwe...
> 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.
>
> Tom Ogilvy wrote:
> >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!
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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


Tom Ogilvy wrote:
>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!

--
Message posted via http://www.officekb.com

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


"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5c03b5667ee8e@uwe...
> 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).
>
>
> Tom Ogilvy wrote:
> >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!
>
> --
> Message posted via http://www.officekb.com