Still Having Trouble with Combo Box

[mtm4300 via OfficeKB.com]

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

 

[Toppers]

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]

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.

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

 

[mtm4300 via OfficeKB.com]

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

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

 

[Toppers]

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
problem(s).([email protected])

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

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

 

[mtm4300 via OfficeKB.com]

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.

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
problem(s).([email protected])

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]

 

[Toppers]

Add this to begiining of macro "CreateAMajor"

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

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.

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
problem(s).([email protected])

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

 

[mtm4300 via OfficeKB.com]

Thank you. Everything is starting to come together!

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]

 

[Toppers]

Good news!

Thank you. Everything is starting to come together!

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