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?

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

[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


--
Regards,
Tom Ogilvy

"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5bacc0b4ad327@uwe...
> 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?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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

Tom Ogilvy wrote:
>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
>
>> 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?

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

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

BadgerMK wrote:
>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
>

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

[Toppers]

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

"mtm4300 via OfficeKB.com" wrote:

> 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?
>
> Tom Ogilvy wrote:
> >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
> >
> >> 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?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1
>

[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)
--
Regards,
Tom Ogilvy


"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:06B04337-BDA5-4BEE-9EC2-F7BA65DFB02C@microsoft.com...
> 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) ?)
>
> "mtm4300 via OfficeKB.com" wrote:
>
> > 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?
> >
> > Tom Ogilvy wrote:
> > >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
> > >
> > >> 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?
> >
> > --
> > Message posted via OfficeKB.com
> > http://www.officekb.com/Uwe/Forums.a...mming/200602/1
> >

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


--
Regards,
Tom Ogilvy




"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5bad10f7b480f@uwe...
> 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?
>
> Tom Ogilvy wrote:
> >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
> >
> >> 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?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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

Tom Ogilvy wrote:
>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?

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

[Toppers]

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

"Tom Ogilvy" wrote:

> 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)
> --
> Regards,
> Tom Ogilvy
>
>
> "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> news:06B04337-BDA5-4BEE-9EC2-F7BA65DFB02C@microsoft.com...
> > 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) ?)
> >
> > "mtm4300 via OfficeKB.com" wrote:
> >
> > > 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?
> > >
> > > Tom Ogilvy wrote:
> > > >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
> > > >
> > > >> 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?
> > >
> > > --
> > > Message posted via OfficeKB.com
> > > http://www.officekb.com/Uwe/Forums.a...mming/200602/1
> > >
>
>
>

[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


"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5bae4a56cab2f@uwe...
> 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.
>
> Tom Ogilvy wrote:
> >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?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1

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

Tom Ogilvy wrote:
>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?

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

[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


"mtm4300 via OfficeKB.com" <u18572@uwe> wrote in message
news:5baf2db35f127@uwe...
> 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!
>
> Tom Ogilvy wrote:
> >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?
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200602/1