Prev: Pivot Items Visible
Next: Help with printing rows and/or columns
From: Christian on 23 Mar 2010 20:38
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
 | 
Pages: 1
Prev: Pivot Items Visible
Next: Help with printing rows and/or columns