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

--
Traa Dy Liooar

Jock

Try this

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


--

HTH

Bob

"Jock" <(E-Mail Removed)> wrote in message
news:A3FD16D6-75E6-45F0-B33C-(E-Mail Removed)...
>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
>
> --
> Traa Dy Liooar
>
> Jock