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" <Jock@discussions.microsoft.com> wrote in message 
news:A3FD16D6-75E6-45F0-B33C-1772FAEF5FE2@microsoft.com...
>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 

Hi, i need something similar but i cant get it to work and this is the first post i see in weeks about what im looking for, but would it be possible for me to see a file with that code and see it work, tried it on me own and it doesn't work.

I tried simpler dependant comboboxes before that i can type each option manualy and have a case for every single option in the Cbox1, but now i cant do it manualy because the second combobox have at least 50 options and in CBox 1 at least 20 options, that would mean me writing at least 1000 diferent options. :S

I know you were asking a question, but you at least have been sucsesful in doing what i need.

expample of what i need. i have column A with Countries, and then for each country a have a seperate column that is titled with one country, followed by cities. This is not the real thing that i have in my project, but its pretty much the same thing, and its simpler to explain.

These columns are all dynamic ranges, so as i fill in more cities o countries the range gets bigger automaticly

Here is an example of how i have the first CBox:

Private Sub UserForm_Initialize()
  Dim cCountry As Range 'cell as range
  Dim ws As Worksheet
  Set ws = Worksheets("World_Map")  'Name of worksheet
  For Each cCountry In ws.Range("Country_List")'Range
    With Me.cbo_Country 'ComboBox1
      .AddItem cCountry.Value
    End With
  Next cCountry 
End Sub

Private Sub cbo_Country_Change()
    Updatecbo_City
End Sub

Sub Updatecbo_City()
    Dim ws As Worksheet
    Set ws = Worksheets("World_Map")

    ws.Activate
    
    Select Case cbo_City
        Case "Canada"
            cbo_City.RowSource = "Canada_City_List"

        Case "U.S.A"
            cbo_City.RowSource = "U.S.A_City_List"

        Case "Mexico"
            cbo_City.RowSource = "Mexico_City_List"
    End Select
    
End Sub

So As you see if i have more info it makes my life pretty tough and i would also need textboxes that are dependent to the second option, but i have that figured out. Just need to fin a code to make my ComboBox2 easier and dynamic.
If you could help i will appreciate, Regards Christian G.



Jock wrote:

Dependant ComboBox on a UserForm
19-Mar-10

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

Previous Posts In This Thread:


Submitted via EggHeadCafe - Software Developer Portal of Choice 
WPF Report Engine, Part 1
http://www.eggheadcafe.com/tutorials/aspnet/31ed1875-728d-4037-9074-4eba5c821311/wpf-report-engine-part-1.aspx