ComboBox processing error

[Jim Berglund]

Can someone please help me with the following? I want the contents of a 'Regions' ComboBox to subset the possible choices in a second Combobox. I get a 1004 error relating to the DropDown variable...

Thanks,
Jim Berglund
_________________________________________________________________________
Private Sub ComboBox1_Change()
Dim drpdwn As DropDown
With ActiveSheet
Set drpdwn = .DropDowns(Application.Caller)
Select Case drpdwn.ListIndex
Case "All"
' no selection, do nothing
Case 1
' Item1 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 127), .Cells(4, 127))
Case 2
' item2 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 128), .Cells(4, 128))
Case 3
' item3 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 129), .Cells(4, 129))
Case 4
' one of item 4 to 14 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 130), ..Cells(4, 130))
Case 5 To 14
' one of item 4 to 14 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 131), ..Cells(4, 131))
End Select

 

[IC]

I don't know if this is the whole problem but you have "DropDowns" in the "Set" line instead of "DropDown".
Can someone please help me with the following? I want the contents of a 'Regions' ComboBox to subset the possible choices in a second Combobox. I get a 1004 error relating to the DropDown variable...

Thanks,
Jim Berglund
_________________________________________________________________________
Private Sub ComboBox1_Change()
Dim drpdwn As DropDown
With ActiveSheet
Set drpdwn = .DropDowns(Application.Caller)
Select Case drpdwn.ListIndex
Case "All"
' no selection, do nothing
Case 1
' Item1 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 127), ..Cells(4, 127))
Case 2
' item2 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 128), ..Cells(4, 128))
Case 3
' item3 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 129), ..Cells(4, 129))
Case 4
' one of item 4 to 14 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 130), ..Cells(4, 130))
Case 5 To 14
' one of item 4 to 14 selected
ComboBox2.ListFillRange = .Range(.Cells(3, 131), ..Cells(4, 131))
End Select

 

[Dave Peterson]

It also looks like the combobox was created from the control toolbox toolbar.

If you replace that combobox with a dropdown from the Forms toolbar and move
your code into a general module, does it work?

Also, the .listindex will always be a number--never "All".

 

[Jim Berglund]

Thanks, I tried your suggestions, but didn't get the results I wanted. I've used forms in the past, but was trying something different. However, I did come up with a visual, but less elegant approach, using if statements that does give me the cascading effect I was looking for. (Shown below...)

Private Sub ComboBox4_Change() 'Supertype
Dim sX As Variant
Application.ScreenUpdating = False

With ActiveSheet
.Range("DP5P200").Clear
sX = .Range("CB3").Text
i = 5

If sX = Range("CX20").Value Then
Else
If sX = Range("CY20").Text Then
While .Cells((i + 16), 103).Text <> ""
.Cells(i, 120).Value = .Cells((i + 16), 103).Value
i = i + 1
Wend
Else
If sX = Range("CZ20").Text Then
While .Cells((i + 16), 104).Text <> ""
.Cells(i, 120).Value = .Cells((i + 16), 104).Value
i = i + 1
Wend
Else
If sX = Range("DA20").Text Then
While .Cells((i + 16), 105).Text <> ""
.Cells(i, 120).Value = .Cells((i + 16), 105).Value
i = i + 1
Wend
Else
If sX = Range("DB20").Text Then
While .Cells((i + 16), 106).Text <> ""
.Cells(i, 120).Value = .Cells((i + 16), 106).Value
i = i + 1
Wend
Else
If sX = Range("DC20").Text Then
While .Cells((i + 16), 107).Text <> ""
.Cells(i, 120).Value = .Cells((i + 16), 107).Value
i = i + 1
Wend
Else
If sX = Range("DD20").Text Then
While .Cells((i + 16), 108).Text <> ""
.Cells(i, 120).Value = .Cells((i + 16), 108).Value
i = i + 1
Wend
End If

End If
End If
End If
End If
End If
End If
End With

End Sub
It creates a list which will be used as the ListFillRange for the second combobox. I'd appreciate more detailed suggestions for a simpler approach, if you feel this is just too ungainly.

FYI, This is a massive data management project that I'm (independently) working on to handle equipment replacement decisions for large fleets based on fleet asset and maintenance history. The data is collected and massaged using Cognos, but the detailed operations are done in Excel.

Regards,
Jim Berglund

 

[Dave Peterson]

I'm not quite sure what you're doing.

But I placed two comboboxes from the control toolbox toolbar (not the Forms
toolbar) on to a worksheet and used this code:

Option Explicit
Dim blkProc As Boolean
Private Sub ComboBox1_Change()

'just for testing!
'MsgBox Me.ComboBox1.ListIndex

blkProc = True
Me.ComboBox2.Value = ""
blkProc = False

With Me
Select Case Me.ComboBox1.ListIndex
Case 0
Me.ComboBox2.ListFillRange _
= .Range(.Cells(3, 127), .Cells(4, 127)) _
.Address(external:=True)
Case 1
Me.ComboBox2.ListFillRange _
= .Range(.Cells(3, 128), .Cells(4, 128)) _
.Address(external:=True)
Case 2
Me.ComboBox2.ListFillRange _
= .Range(.Cells(3, 129), .Cells(4, 129)) _
.Address(external:=True)
Case 3
Me.ComboBox2.ListFillRange _
= .Range(.Cells(3, 130), .Cells(4, 130)) _
.Address(external:=True)
Case 4 To 13
ComboBox2.ListFillRange _
= .Range(.Cells(3, 131), .Cells(4, 131)) _
.Address(external:=True)
End Select
End With
End Sub
Private Sub ComboBox2_Change()
If blkProc Then Exit Sub
MsgBox "you changed it to: " & Me.ComboBox2.Value
End Sub

When the first item is selected, it's .listindex = 0.

And I wasn't sure if you had any code for when the 2nd combobox changed--but if
it's changed in code, you may want to block that code from running (that's what
the BlkProc variable does).

The .listfillrange is looking for a string. The .address(external:=true) did
that.

If you used the dropdowns from the Forms toolbar, the idea will be similar--but
it will be implemented differently.

And one more thing...

These newsgroups are pretty much plain text--no HTML postings and no
attachments. Can you please post in plain text. It'll make it easier for some
of us older people to read your posts.

(and you may find you get more responses!)