| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Still Having Trouble with Combo Box
Tom O. helped me with a problem I had creating a varying combo box. I used
the code, but I am still having errors. The purpose of the program is to eliminate a variety of outputs for the user (42 in all). I have two option butions that will run a combobox. Each combobox (I have two) has 3 different choices. From there, the user will select one of the 3 choices. The user's selection will run another combobox. This combobox has 7 choices for the user. The following code is only a part of the code. I get two errors when I run this. - "Permission Denied" - And then the ".Name" creates an error. If anyone can offer me any help, I would appreciate it. Sub optmeasureAmaj_Click() If optmeasureE.Value = True Then CreateAMajor End If End Sub Sub optmeasureAmin_Click() If optmeasureM.Value = True Then CreateAMinor End If End Sub Sub CreateAMajor() With Worksheets(1).Shapes.AddFormControl(xlDropDown, _ Left:=245, Top:=188, Width:=192, Height:=15) .ControlFormat.DropDownLines = 3 .ControlFormat.AddItem "A", 1 .ControlFormat.AddItem "B", 2 .ControlFormat.AddItem "C*", 3 .Name = "CreateAMajor" .OnAction = "cboChoice" End With End Sub Sub cboCoice() Dim idex As Long On Error Resume Next Worksheets(1).DropDowns("cboChoice").Delete On Error GoTo 0 idex = Worksheets(1).DropDowns("CreateAMajor").ListIndex With Worksheets(1).Shapes.AddFormControl(xlDropDown, _ Left:=245, Top:=359, Width:=192, Height:=15) .ControlFormat.DropDownLines = 7 .Name = "cboCoice" Select Case idex Case 1 .ControlFormat.AddItem "A1", 1 .ControlFormat.AddItem "A2", 2 .ControlFormat.AddItem "A3", 3 .ControlFormat.AddItem "A4", 4 .ControlFormat.AddItem "A5", 5 .ControlFormat.AddItem "A6", 6 .ControlFormat.AddItem "A7", 7 .OnAction = "cboChoiceA_Click" Case 2 .ControlFormat.AddItem "B1", 1 .ControlFormat.AddItem "B2", 2 .ControlFormat.AddItem "B3", 3 .ControlFormat.AddItem "B4", 4 .ControlFormat.AddItem "B5", 5 .ControlFormat.AddItem "B6", 6 .ControlFormat.AddItem "B7", 7 .OnAction = "cboChoiceB_Click" Case 3 .ControlFormat.AddItem "C1", 1 .ControlFormat.AddItem "C2", 2 .ControlFormat.AddItem "C3", 3 .ControlFormat.AddItem "C4", 4 .ControlFormat.AddItem "C5", 5 .ControlFormat.AddItem "C6", 6 .ControlFormat.AddItem "C7", 7 .OnAction = "cboChoiceC_Click" End Select End With End Sub -- Message posted via http://www.officekb.com |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Having Trouble with Combo Box
Hi,
This suggests you are trying to create a combobox (CreateAMajor?) which already exists. Add code to delete as per Tom's example for "cboCoice" ... typo ? cboChoice? ..... but once created should it not remain until worksheet closes? "mtm4300 via OfficeKB.com" wrote: Tom O. helped me with a problem I had creating a varying combo box. I used the code, but I am still having errors. The purpose of the program is to eliminate a variety of outputs for the user (42 in all). I have two option butions that will run a combobox. Each combobox (I have two) has 3 different choices. From there, the user will select one of the 3 choices. The user's selection will run another combobox. This combobox has 7 choices for the user. The following code is only a part of the code. I get two errors when I run this. - "Permission Denied" - And then the ".Name" creates an error. If anyone can offer me any help, I would appreciate it. Sub optmeasureAmaj_Click() If optmeasureE.Value = True Then CreateAMajor End If End Sub Sub optmeasureAmin_Click() If optmeasureM.Value = True Then CreateAMinor End If End Sub Sub CreateAMajor() With Worksheets(1).Shapes.AddFormControl(xlDropDown, _ Left:=245, Top:=188, Width:=192, Height:=15) .ControlFormat.DropDownLines = 3 .ControlFormat.AddItem "A", 1 .ControlFormat.AddItem "B", 2 .ControlFormat.AddItem "C*", 3 .Name = "CreateAMajor" .OnAction = "cboChoice" End With End Sub Sub cboCoice() Dim idex As Long On Error Resume Next Worksheets(1).DropDowns("cboChoice").Delete On Error GoTo 0 idex = Worksheets(1).DropDowns("CreateAMajor").ListIndex With Worksheets(1).Shapes.AddFormControl(xlDropDown, _ Left:=245, Top:=359, Width:=192, Height:=15) .ControlFormat.DropDownLines = 7 .Name = "cboCoice" Select Case idex Case 1 .ControlFormat.AddItem "A1", 1 .ControlFormat.AddItem "A2", 2 .ControlFormat.AddItem "A3", 3 .ControlFormat.AddItem "A4", 4 .ControlFormat.AddItem "A5", 5 .ControlFormat.AddItem "A6", 6 .ControlFormat.AddItem "A7", 7 .OnAction = "cboChoiceA_Click" Case 2 .ControlFormat.AddItem "B1", 1 .ControlFormat.AddItem "B2", 2 .ControlFormat.AddItem "B3", 3 .ControlFormat.AddItem "B4", 4 .ControlFormat.AddItem "B5", 5 .ControlFormat.AddItem "B6", 6 .ControlFormat.AddItem "B7", 7 .OnAction = "cboChoiceB_Click" Case 3 .ControlFormat.AddItem "C1", 1 .ControlFormat.AddItem "C2", 2 .ControlFormat.AddItem "C3", 3 .ControlFormat.AddItem "C4", 4 .ControlFormat.AddItem "C5", 5 .ControlFormat.AddItem "C6", 6 .ControlFormat.AddItem "C7", 7 .OnAction = "cboChoiceC_Click" End Select End With End Sub -- Message posted via http://www.officekb.com |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Having Trouble with Combo Box
Yes. It is suppose to be "cbochoice" and it should remain until the worksheet
is closed. Im still having trouble with it working though. I did notice that I had some naming errors in my posted code, but I have fixed them. Toppers wrote: Hi, This suggests you are trying to create a combobox (CreateAMajor?) which already exists. Add code to delete as per Tom's example for "cboCoice" ... typo ? cboChoice? .... but once created should it not remain until worksheet closes? Tom O. helped me with a problem I had creating a varying combo box. I used the code, but I am still having errors. The purpose of the program is to [quoted text clipped - 85 lines] End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200602/1 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Having Trouble with Combo Box
I hate to be a pest, but I am still having trouble executing this process.
There are several errors that occur. Here is the code I have. I didnt put the optmeasureM code here, because it will almost be the same as the optmeasureE code. If anyone could try to run this and help me figure the problems, I would greatly appreciate it. Thank you. Also the name of my worksheet is 'Program' Sub optmeasureE_Click() ' Determines if the user executes the program in English units. If optmeasureE.Value = True Then CreateCombobox1 End If End Sub Sub optmeasureM_Click() CreateCombobox3 ' Determines if the user executes the program in Metric units. If optmeasureM.Value = True Then End If End Sub Sub CreateCombobox1() Dim newname As Worksheet Set newname = Sheets("Program") 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 = "Combobox1" .OnAction = "Combobox1_Change" End With End Sub Sub Combobox1_Change() Dim idex As Long Dim newname As Worksheet Set newname = Sheets("Program") On Error Resume Next Worksheets(1).DropDowns("Combobox2").Delete On Error GoTo 0 idex = Worksheets(1).DropDowns("Combobox1").ListIndex With Worksheets(1).Shapes.AddFormControl(xlDropDown, _ Left:=245, Top:=309, Width:=192, Height:=15) .ControlFormat.DropDownLines = 7 .Name = "Combobox2" 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 = "Combobox2A_Click" Case 2 .ControlFormat.AddItem "E2: Circular Corrugated Pipe", 1 .ControlFormat.AddItem "E2: Circular Corrugated Pipe (SPM)", 2 .ControlFormat.AddItem "E2: Circular Smooth-Interior Pipe", 3 .ControlFormat.AddItem "E2: Deformed Corrugated Pipe", 4 .ControlFormat.AddItem "E2: Deformed Corrugated Pipe (SPAA)", 5 .ControlFormat.AddItem "E2: Deformed Corrugated PIpe (SPS)", 6 .ControlFormat.AddItem "E2: Deformed Smooth-Interior Pipe", 7 .OnAction = "Combobox2B_Click" Case 3 .ControlFormat.AddItem "E3: Circular Corrugated Pipe", 1 .ControlFormat.AddItem "E3: Circular Corrugated Pipe (SPM)", 2 .ControlFormat.AddItem "E3: Circular Smooth-Interior Pipe", 3 .ControlFormat.AddItem "E3: Deformed Corrugated Pipe", 4 .ControlFormat.AddItem "E3: Deformed Corrugated Pipe (SPAA)", 5 .ControlFormat.AddItem "E3: Deformed Corrugated PIpe (SPS)", 6 .ControlFormat.AddItem "E3: Deformed Smooth-Interior Pipe", 7 .OnAction = "Combobox2C_Click" End Select End With End Sub Toppers wrote: Hi, This suggests you are trying to create a combobox (CreateAMajor?) which already exists. Add code to delete as per Tom's example for "cboCoice" ... typo ? cboChoice? .... but once created should it not remain until worksheet closes? Tom O. helped me with a problem I had creating a varying combo box. I used the code, but I am still having errors. The purpose of the program is to [quoted text clipped - 85 lines] End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200602/1 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Having Trouble with Combo Box
Hi,
I ran your code as posted and only problem(s) I found was that the macros Combobox2A_Click (and 2B,2C) were missing - these are actioned when you select from the second combobox - so I just added these as below and everything worked. I was puzzled by the If optmeasureE.Value = True statement (how is 'optmeasureE' defined?) so I improvised. If you continue to have problems, post me the workbook and explain the ) Sub Combobox2A_Click() MsgBox "Combobox2A_Click called" End Sub "mtm4300 via OfficeKB.com" wrote: I hate to be a pest, but I am still having trouble executing this process. There are several errors that occur. Here is the code I have. I didnt put the optmeasureM code here, because it will almost be the same as the optmeasureE code. If anyone could try to run this and help me figure the problems, I would greatly appreciate it. Thank you. Also the name of my worksheet is 'Program' Sub optmeasureE_Click() ' Determines if the user executes the program in English units. If optmeasureE.Value = True Then CreateCombobox1 End If End Sub Sub optmeasureM_Click() CreateCombobox3 ' Determines if the user executes the program in Metric units. If optmeasureM.Value = True Then End If End Sub Sub CreateCombobox1() Dim newname As Worksheet Set newname = Sheets("Program") 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 = "Combobox1" .OnAction = "Combobox1_Change" End With End Sub Sub Combobox1_Change() Dim idex As Long Dim newname As Worksheet Set newname = Sheets("Program") On Error Resume Next Worksheets(1).DropDowns("Combobox2").Delete On Error GoTo 0 idex = Worksheets(1).DropDowns("Combobox1").ListIndex With Worksheets(1).Shapes.AddFormControl(xlDropDown, _ Left:=245, Top:=309, Width:=192, Height:=15) .ControlFormat.DropDownLines = 7 .Name = "Combobox2" 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 = "Combobox2A_Click" Case 2 .ControlFormat.AddItem "E2: Circular Corrugated Pipe", 1 .ControlFormat.AddItem "E2: Circular Corrugated Pipe (SPM)", 2 .ControlFormat.AddItem "E2: Circular Smooth-Interior Pipe", 3 .ControlFormat.AddItem "E2: Deformed Corrugated Pipe", 4 .ControlFormat.AddItem "E2: Deformed Corrugated Pipe (SPAA)", 5 .ControlFormat.AddItem "E2: Deformed Corrugated PIpe (SPS)", 6 .ControlFormat.AddItem "E2: Deformed Smooth-Interior Pipe", 7 .OnAction = "Combobox2B_Click" Case 3 .ControlFormat.AddItem "E3: Circular Corrugated Pipe", 1 .ControlFormat.AddItem "E3: Circular Corrugated Pipe (SPM)", 2 .ControlFormat.AddItem "E3: Circular Smooth-Interior Pipe", 3 .ControlFormat.AddItem "E3: Deformed Corrugated Pipe", 4 .ControlFormat.AddItem "E3: Deformed Corrugated Pipe (SPAA)", 5 .ControlFormat.AddItem "E3: Deformed Corrugated PIpe (SPS)", 6 .ControlFormat.AddItem "E3: Deformed Smooth-Interior Pipe", 7 .OnAction = "Combobox2C_Click" End Select End With End Sub Toppers wrote: Hi, This suggests you are trying to create a combobox (CreateAMajor?) which already exists. Add code to delete as per Tom's example for "cboCoice" ... typo ? cboChoice? .... but once created should it not remain until worksheet closes? Tom O. helped me with a problem I had creating a varying combo box. I used the code, but I am still having errors. The purpose of the program is to [quoted text clipped - 85 lines] End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200602/1 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Having Trouble with Combo Box
I wasnt able to open your email. However, I did get both of my comboboxes
working properly. I am still having trouble if I run it twice. If combobox1 is already showing, I cannot run the macro again. I know that I must clear/delete the combobox1 from the worksheet. Also, when I start the program one of the option buttons is already clicked. Is there anyway to have neither button clicked? Thank you for your help. Toppers wrote: Hi, I ran your code as posted and only problem(s) I found was that the macros Combobox2A_Click (and 2B,2C) were missing - these are actioned when you select from the second combobox - so I just added these as below and everything worked. I was puzzled by the If optmeasureE.Value = True statement (how is 'optmeasureE' defined?) so I improvised. If you continue to have problems, post me the workbook and explain the ) Sub Combobox2A_Click() MsgBox "Combobox2A_Click called" End Sub I hate to be a pest, but I am still having trouble executing this process. There are several errors that occur. Here is the code I have. I didnt put the [quoted text clipped - 102 lines] End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200602/1 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Still Having Trouble with Combo Box
Add this to begiining of macro "CreateAMajor"
On Error Resume Next Worksheets(1).DropDowns("CreateAMajor").Delete On Error GoTo 0 "mtm4300 via OfficeKB.com" wrote: I wasnt able to open your email. However, I did get both of my comboboxes working properly. I am still having trouble if I run it twice. If combobox1 is already showing, I cannot run the macro again. I know that I must clear/delete the combobox1 from the worksheet. Also, when I start the program one of the option buttons is already clicked. Is there anyway to have neither button clicked? Thank you for your help. Toppers wrote: Hi, I ran your code as posted and only problem(s) I found was that the macros Combobox2A_Click (and 2B,2C) were missing - these are actioned when you select from the second combobox - so I just added these as below and everything worked. I was puzzled by the If optmeasureE.Value = True statement (how is 'optmeasureE' defined?) so I improvised. If you continue to have problems, post me the workbook and explain the ) Sub Combobox2A_Click() MsgBox "Combobox2A_Click called" End Sub I hate to be a pest, but I am still having trouble executing this process. There are several errors that occur. Here is the code I have. I didnt put the [quoted text clipped - 102 lines] End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200602/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| linking a form combo box... results from the combo box to another | Excel Discussion (Misc queries) | |||
| combo reference on another combo box for picking address etc. | Excel Worksheet Functions | |||
| "Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
| "Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
| trouble with combo box | Excel Programming | |||
Hybrid Mode
