ExcelBanter - Still Having Trouble with Combo Box

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Still Having Trouble with Combo Box (https://www.excelbanter.com/excel-programming/353162-still-having-trouble-combo-box.html)

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

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

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

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

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

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

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

Still Having Trouble with Combo Box

Thank you. Everything is starting to come together!

Toppers wrote:
Add this to begiining of macro "CreateAMajor"

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

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
[quoted text clipped - 22 lines]

End Sub

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

Still Having Trouble with Combo Box

Good news!

"mtm4300 via OfficeKB.com" wrote:

Thank you. Everything is starting to come together!

Toppers wrote:
Add this to begiining of macro "CreateAMajor"

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

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
[quoted text clipped - 22 lines]

End Sub

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