Combo Box to Combo Box Selections

[mtm4300 via OfficeKB.com]

I have combobox1 that has 3 choices available for the user to choose from. I
have combobox2 that has seven choices to choose from. However, the seven
choices will vary depending on what the user picked in combobox1. So for each
choice the user makes in combobox1, the seven choices will change in
combobox2. I am having trouble getting combobox2 to change based on the
selection in combobox1. Can anyone help me?

 

[Tom Ogilvy]

Private Sub Combobox1_Click()
Combobox2.clear
With Worksheets("Sheet1")
Select Case Combobox1.ListIndex
Case 0
Combobox2.List = .Range("Item1")
Case 1
Combobox2.List = .Range("Item2")
Case 2
Combobox2.List = .Range("Item3"
Case Else
Combobox2.Clear
End Select
End Sub

 

[BadgerMK]

Need to do this is 2 stages

firstly the following code will run each time value in combo1 is
changed, and needs to go in the Worksheet_Change(ByVal Target As
Range)

intRowNum = ActiveCell.Row
strAddress = Target.Address
If Left(strAddress, 3) = "$B$" Then
UpdateDropDown
Else
Exit Sub
End If

Secondly this will run the UpdateDropDown function which changes the
source values of Cmb2 depending on value selected in cmb1, each cmb1
value should have a matching cmb2 list, I use range names for each
list

UpdateDropDown function has this code as it's core

Case "CAPITAL"
Range("c" & intRowNum).Validation.Delete
Range("c" & intRowNum).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=RangeGetIt2"
Range("c" & intRowNum).Validation.IgnoreBlank = True
Range("c" & intRowNum).Validation.InCellDropdown = True
Range("c" & intRowNum).Validation.ShowInput = True
Range("c" & intRowNum).Validation.ShowError = True
Case "INCOME"
Range("c" & intRowNum).Validation.Delete
Range("c" & intRowNum).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=RangeGetIt3"
Range("c" & intRowNum).Validation.IgnoreBlank = True
Range("c" & intRowNum).Validation.InCellDropdown = True
Range("c" & intRowNum).Validation.ShowInput = True
Range("c" & intRowNum).Validation.ShowError = True
Case Else
Range("c" & intRowNum).Validation.Delete
Range("c" & intRowNum).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=RangeGetIt"
Range("c" & intRowNum).Validation.IgnoreBlank = True
Range("c" & intRowNum).Validation.InCellDropdown = True
Range("c" & intRowNum).Validation.ShowInput = True
Range("c" & intRowNum).Validation.ShowError = True
End Select

 

[mtm4300 via OfficeKB.com]

I have the first combo box with three choices. If they select "1" then I want
ComboboxA to appear. If they select "2" I want ComboboxB to appear and so on.
Everytime I try this I get an error that says "Object Required" and I almost
positive that I am entering the code correctly. Can you help with this
situation?

 

[mtm4300 via OfficeKB.com]

I am a beginner with VBA so I dont quite understand everything you wrote. I
dont know what to change and what to keep. Also the statement.

Range("c" & intRowNum).Validation.Add Type:=xlValidateList,
AlertStyle:=xlValidAlertStop, Operator:=xlBetween,
Formula1:="=RangeGetIt2"

appears in red. Do I need to change the format of it?

 

[Guest]

Hi,
Tom interpretted your first posting (as did I) as wanting to place
a different list of values in Combobox2 based on the value from Combobox1..
In Tom's code these are named ranges (Item1, Item2 and Item3). You last posts
suggests you want a different Combobox to appear .. or are ComboboxA, B and C
named ranges? If the latter, replace Item1,2 etc with ComboboxA,b etc.

(Hope I got this right Tom. FYI, I can't get the Combobox2.List to work ...
only Rowsource works for me. To get List to work, I have to assign my named
range to a variant array and use Combobox2.list()=MyArray .. so what am I
doing wrong (XL2003) ?)

 

[Tom Ogilvy]

To eliminate such recalcitrant behavior, use .value

Private Sub Combobox1_Click()
ComboBox2.Clear
With Worksheets("Sheet1")
Select Case ComboBox1.ListIndex
Case 0
ComboBox2.List = .Range("Item1").Value
Case 1
ComboBox2.List = .Range("Item2").Value
Case 2
ComboBox2.List = .Range("Item3").Value
Case Else
ComboBox2.Clear
End Select
End With
End Sub

Tested in a the worksheet, but should work as well in a userform. Make sure
the RowSource/ListFillRange is not set (which I know you have)

 

[Tom Ogilvy]

Where are your comboboxes located? What type of comboboxes are they (forms
toolbar, control toolbox toolbar, data validation)?

when you say you want them to appear, where do you want them to appear and
why wouldn't it be better just to use one Combobox for the second combobox
and change what appears in the dropdown. where are they now.

Since you say you don't know much about coding, then the more specific
information you supply, the less people will have to guess at what you are
trying to do and the fewer changes you will have to make to their code.

Revised code: for control toolbox toolbar comboboxes on a worksheet
(probably a worksheet named sheet1) where Combobox2 does not have an
assignment to ListFillRange. Assumes you have named ranges on Sheet1
(Item1, Item2, Item3) that specify what values to put in Combobox2's
dropdown.

Private Sub Combobox1_Click()
me.ComboBox2.Clear
With Worksheets("Sheet1")
Select Case me.ComboBox1.ListIndex
Case 0
me.ComboBox2.List = .Range("Item1").Value
Case 1
me.ComboBox2.List = .Range("Item2").Value
Case 2
me.ComboBox2.List = .Range("Item3").Value
Case Else
me.ComboBox2.Clear
End Select
End With
End Sub

for a userform, refreshingly, the code would be the same.

 

[mtm4300 via OfficeKB.com]

I have combobox1 with choice "A","B" and "C". If the user chooses "A" then I
want another combobox to appear to show the seven categories of "A". And the
same goes for "B" and "C". I thought it would be easier to create 3 different
boxes for combobox 2. Here is the code I have used so far for combobox1.

Sub EMethodbox_Change()
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=187, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 3
.ControlFormat.AddItem "A", 1
.ControlFormat.AddItem "B", 2
.ControlFormat.AddItem "C", 3
End With
End Sub
_________________________________
'And here is the code I have used for one choice in combobox2.

Sub E1PipeTypeBox_Change()

With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=359, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 7
.ControlFormat.AddItem "1", 1
.ControlFormat.AddItem "2", 2
.ControlFormat.AddItem "3", 3
.ControlFormat.AddItem "4", 4
.ControlFormat.AddItem "5", 5
.ControlFormat.AddItem "6", 6
.ControlFormat.AddItem "7", 7
End With
End Sub
___________________________________
I have tried to use .Value, but I know I must be doing something wrong. I was
planning on having a different Subroutine for each choice, and after the
user's selection, the specific subroutine would run displaying the seven
categories in combobox2. If there is an easier way then please let me know. I
hope this helps, and thank you for helping me. I really appreciate it.

Where are your comboboxes located? What type of comboboxes are they (forms
toolbar, control toolbox toolbar, data validation)?

when you say you want them to appear, where do you want them to appear and
why wouldn't it be better just to use one Combobox for the second combobox
and change what appears in the dropdown. where are they now.

Since you say you don't know much about coding, then the more specific
information you supply, the less people will have to guess at what you are
trying to do and the fewer changes you will have to make to their code.

Revised code: for control toolbox toolbar comboboxes on a worksheet
(probably a worksheet named sheet1) where Combobox2 does not have an
assignment to ListFillRange. Assumes you have named ranges on Sheet1
(Item1, Item2, Item3) that specify what values to put in Combobox2's
dropdown.

Private Sub Combobox1_Click()
me.ComboBox2.Clear
With Worksheets("Sheet1")
Select Case me.ComboBox1.ListIndex
Case 0
me.ComboBox2.List = .Range("Item1").Value
Case 1
me.ComboBox2.List = .Range("Item2").Value
Case 2
me.ComboBox2.List = .Range("Item3").Value
Case Else
me.ComboBox2.Clear
End Select
End With
End Sub

for a userform, refreshingly, the code would be the same.

I have the first combo box with three choices. If they select "1" then I want
ComboboxA to appear. If they select "2" I want ComboboxB to appear and so on.

[quoted text clipped - 23 lines]

 

[Guest]

Tom, Thanks once again. So much learning to do!

To eliminate such recalcitrant behavior, use .value

Private Sub Combobox1_Click()
ComboBox2.Clear
With Worksheets("Sheet1")
Select Case ComboBox1.ListIndex
Case 0
ComboBox2.List = .Range("Item1").Value
Case 1
ComboBox2.List = .Range("Item2").Value
Case 2
ComboBox2.List = .Range("Item3").Value
Case Else
ComboBox2.Clear
End Select
End With
End Sub

Tested in a the worksheet, but should work as well in a userform. Make sure
the RowSource/ListFillRange is not set (which I know you have)

 

[Tom Ogilvy]

Your code indicates you are using dropdown boxes from the forms toolbar.

Your first piece of code puts in a dropdown box with 3 choices, so it must
be for creating what you call Combobox1. When you select a choice, it
should then call the other procedure to create your Combobox2. the other
procedure is assigned to Combobox1 as its OnAction property. I will go
with the generic Combobox1 and Combobox2 since I could misinterpret the
names you have used and confuse you further.

Sub CreateCombobox1()
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=187, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 3
.ControlFormat.AddItem "A", 1
.ControlFormat.AddItem "B", 2
.ControlFormat.AddItem "C", 3
.Name = "Combobox1"
.OnAction = "Combobox1_Change"
End With
End Sub


the above sub has to be run either manually or by some event to create the
first combobox. After created, if a value is selected by the user from
Combobox1, then the Combobox1_Change macro runs because we have assigned it
to the onaction property.


Sub Combobox1_Change()
Dim idex as Long
' delete any existing dropdown box named Combobox2
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:=288, Top:=359, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 7
.Name = "Combobox2"
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 = "Combobox2A_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 = "Combobox2B_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 = "Combobox2C_Click"

End Select

End With
End Sub

So you assign different click event macros to the Combobox2 based on the
selection from Combobox1.

I also give any box I create, a specific name, so I can refer to it later.


--
Regards,
Tom Ogilvy

I have combobox1 with choice "A","B" and "C". If the user chooses "A" then I
want another combobox to appear to show the seven categories of "A". And the
same goes for "B" and "C". I thought it would be easier to create 3 different
boxes for combobox 2. Here is the code I have used so far for combobox1.

Sub EMethodbox_Change()
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=187, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 3
.ControlFormat.AddItem "A", 1
.ControlFormat.AddItem "B", 2
.ControlFormat.AddItem "C", 3
End With
End Sub
_________________________________
'And here is the code I have used for one choice in combobox2.

Sub E1PipeTypeBox_Change()

With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=359, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 7
.ControlFormat.AddItem "1", 1
.ControlFormat.AddItem "2", 2
.ControlFormat.AddItem "3", 3
.ControlFormat.AddItem "4", 4
.ControlFormat.AddItem "5", 5
.ControlFormat.AddItem "6", 6
.ControlFormat.AddItem "7", 7
End With
End Sub
___________________________________
I have tried to use .Value, but I know I must be doing something wrong. I was
planning on having a different Subroutine for each choice, and after the
user's selection, the specific subroutine would run displaying the seven
categories in combobox2. If there is an easier way then please let me know. I
hope this helps, and thank you for helping me. I really appreciate it.

Where are your comboboxes located? What type of comboboxes are they (forms
toolbar, control toolbox toolbar, data validation)?

when you say you want them to appear, where do you want them to appear and
why wouldn't it be better just to use one Combobox for the second combobox
and change what appears in the dropdown. where are they now.

Since you say you don't know much about coding, then the more specific
information you supply, the less people will have to guess at what you are
trying to do and the fewer changes you will have to make to their code.

Revised code: for control toolbox toolbar comboboxes on a worksheet
(probably a worksheet named sheet1) where Combobox2 does not have an
assignment to ListFillRange. Assumes you have named ranges on Sheet1
(Item1, Item2, Item3) that specify what values to put in Combobox2's
dropdown.

Private Sub Combobox1_Click()
me.ComboBox2.Clear
With Worksheets("Sheet1")
Select Case me.ComboBox1.ListIndex
Case 0
me.ComboBox2.List = .Range("Item1").Value
Case 1
me.ComboBox2.List = .Range("Item2").Value
Case 2
me.ComboBox2.List = .Range("Item3").Value
Case Else
me.ComboBox2.Clear
End Select
End With
End Sub

for a userform, refreshingly, the code would be the same.

I have the first combo box with three choices. If they select "1" then I want
ComboboxA to appear. If they select "2" I want ComboboxB to appear and

so on.
[quoted text clipped - 23 lines]

combobox2. I am having trouble getting combobox2 to change based on the
selection in combobox1. Can anyone help me?

 

[mtm4300 via OfficeKB.com]

I get an error when I run the sub:

the macro 'draftback.xls!EMethodbox_Change' cannot be found

Where EMethodbox = Combobox1

When I run the entire program I get an error:

Runtime error '70'
Permission Denied

Do you know what I am doing wrong?

Thank you very much for your help!

Your code indicates you are using dropdown boxes from the forms toolbar.

Your first piece of code puts in a dropdown box with 3 choices, so it must
be for creating what you call Combobox1. When you select a choice, it
should then call the other procedure to create your Combobox2. the other
procedure is assigned to Combobox1 as its OnAction property. I will go
with the generic Combobox1 and Combobox2 since I could misinterpret the
names you have used and confuse you further.

Sub CreateCombobox1()
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=187, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 3
.ControlFormat.AddItem "A", 1
.ControlFormat.AddItem "B", 2
.ControlFormat.AddItem "C", 3
.Name = "Combobox1"
.OnAction = "Combobox1_Change"
End With
End Sub

the above sub has to be run either manually or by some event to create the
first combobox. After created, if a value is selected by the user from
Combobox1, then the Combobox1_Change macro runs because we have assigned it
to the onaction property.

Sub Combobox1_Change()
Dim idex as Long
' delete any existing dropdown box named Combobox2
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:=288, Top:=359, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 7
.Name = "Combobox2"
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 = "Combobox2A_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 = "Combobox2B_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 = "Combobox2C_Click"

End Select

End With
End Sub

So you assign different click event macros to the Combobox2 based on the
selection from Combobox1.

I also give any box I create, a specific name, so I can refer to it later.

I have combobox1 with choice "A","B" and "C". If the user chooses "A" then I
want another combobox to appear to show the seven categories of "A". And the

[quoted text clipped - 74 lines]

 

[Tom Ogilvy]

You have declared EMethodbox_Change
as

Private Sub Emethodbox_Change()

It should be Public

or you have place it in a Sheet Module or the ThisWorkbook Module - it
should be in a general modue (insert=>Module in the vbe)

or you have done both.

--
Regards,
Tom Ogilvy

I get an error when I run the sub:

the macro 'draftback.xls!EMethodbox_Change' cannot be found

Where EMethodbox = Combobox1

When I run the entire program I get an error:

Runtime error '70'
Permission Denied

Do you know what I am doing wrong?

Thank you very much for your help!

Your code indicates you are using dropdown boxes from the forms toolbar.

Your first piece of code puts in a dropdown box with 3 choices, so it must
be for creating what you call Combobox1. When you select a choice, it
should then call the other procedure to create your Combobox2. the other
procedure is assigned to Combobox1 as its OnAction property. I will go
with the generic Combobox1 and Combobox2 since I could misinterpret the
names you have used and confuse you further.

Sub CreateCombobox1()
With Worksheets(1).Shapes.AddFormControl(xlDropDown, _
Left:=288, Top:=187, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 3
.ControlFormat.AddItem "A", 1
.ControlFormat.AddItem "B", 2
.ControlFormat.AddItem "C", 3
.Name = "Combobox1"
.OnAction = "Combobox1_Change"
End With
End Sub

the above sub has to be run either manually or by some event to create the
first combobox. After created, if a value is selected by the user from
Combobox1, then the Combobox1_Change macro runs because we have assigned it
to the onaction property.

Sub Combobox1_Change()
Dim idex as Long
' delete any existing dropdown box named Combobox2
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:=288, Top:=359, Width:=192, Height:=15)
.ControlFormat.DropDownLines = 7
.Name = "Combobox2"
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 = "Combobox2A_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 = "Combobox2B_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 = "Combobox2C_Click"

End Select

End With
End Sub

So you assign different click event macros to the Combobox2 based on the
selection from Combobox1.

I also give any box I create, a specific name, so I can refer to it later.

I have combobox1 with choice "A","B" and "C". If the user chooses "A" then I
want another combobox to appear to show the seven categories of "A".

And the
[quoted text clipped - 74 lines]

combobox2. I am having trouble getting combobox2 to change based on the
selection in combobox1. Can anyone help me?