 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / February 2006Cases 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! 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  SignatureRegards, Tom Ogilvy > 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! 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. > [quoted text clipped - 8 lines] >> change the cells each time a user clicks on a different option. Any help >> would be greatly appreciated! 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. SignatureRegards, 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 [quoted text clipped - 14 lines] > >> change the cells each time a user clicks on a different option. Any help > >> would be greatly appreciated! 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 > [quoted text clipped - 24 lines] >> >> change the cells each time a user clicks on a different option. Any help >> >> would be greatly appreciated! 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 SignatureRegards, 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 [quoted text clipped - 31 lines] > >> >> change the cells each time a user clicks on a different option. Any help > >> >> would be greatly appreciated! 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 [quoted text clipped - 51 lines] >> >> >> change the cells each time a user clicks on a different option. Any help >> >> >> would be greatly appreciated! 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. SignatureRegards, Tom Ogilvy > Here is what I am trying to do. In the code I have: > [quoted text clipped - 63 lines] > >> >> >> change the cells each time a user clicks on a different option. Any help > >> >> >> would be greatly appreciated! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.