Dependant ComboBox on a UserForm

J

Jock

I have been successful in getting CB2 to be dependant on the value selected
in CB1 but only with single word items. When I try to adapt the code to use
two or more word options from CB1, I get the debug window. I realise why this
is happening - not allowed spaces or illegal characters - so I have been
trying to use OFFSET to get around this but to no avail.
I am losing hair over this one...
So....how can I get a list of meaningful multi word options in CB1 upon
which CB2 options are dependant?

UserForm code (adapted from PM's code) :

Option Explicit

Private Sub UserForm_Initialize()
Dim cell As Range
For Each cell In Worksheets("claims").Range("ClType").Cells
ComboBox1.AddItem cell.Value
Next
ComboBox1.Text = " < Claim type > "
End Sub

Private Sub ComboBox1_Change()
populateCB2 ComboBox1.Value
End Sub

Sub populateCB2(WotClaim As String)
Dim cell As Range
If ComboBox1.ListIndex = -1 Then Exit Sub
ComboBox2.Clear

For Each cell In Worksheets("Claims").Range("val." & WotClaim).Cells
ComboBox2.AddItem cell.Value
Next
ComboBox2.Text = " < Claim value > "

End Sub

Thanks
 
B

Bob Phillips

Try this

Private Sub ComboBox1_Change()
populateCB2 Replace(ComboBox1.Value, " ", "")
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top