Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo boxes
Hi All,
I have 12 named ranges say Jan,Feb,.........Dec these 12 ranges falls in 4 categories say Qrt1, Qrt2, Qrt3, Qrt4 so I make another 4 lists, 1st one, Jan,Feb,Mar and named it as Qrt1 so on. Can I have 3 comboboxes in a userform so that 1st one shows Qrt1,Qrt2, Qrt3, Qrt4 and when Qrt3 is selected the 2nd combobox populate with the list Jul,Aug,Sep and when Aug is selected the 3rd combobox populate with the list named Aug. A sample code is very much appreciated. Cecil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo boxes
Private Sub Userform_Initialize()
ComboBox1.Clear ComboBox2.Clear ComboBox3.Clear ComboBox1.Value = "" ComboBox2.Value = "" ComboBox3.Value = "" ComboBox1.AddItem "Qrt1" ComboBox1.AddItem "Qrt2" ComboBox1.AddItem "Qrt3" ComboBox1.AddItem "Qrt4" End Sub Private Sub Combobox1_Click() Dim lngVal As Long, i As Long ComboBox2.Clear ComboBox3.Clear ComboBox2.Value = "" ComboBox3.Value = "" lngVal = ComboBox1.ListIndex For i = lngVal * 3 + 1 To lngVal * 3 + 3 ComboBox2.AddItem Format(DateSerial(2003, i, 1), "mmm") Debug.Print Format(DateSerial(2003, i, 1), "mmm") Next End Sub Private Sub Combobox2_Click() ComboBox3.Clear ComboBox3.List = Range(ComboBox2.Text).Value End Sub -- Regards, Tom Ogilvy "Cecilkumara Fernando" wrote in message ... Hi All, I have 12 named ranges say Jan,Feb,.........Dec these 12 ranges falls in 4 categories say Qrt1, Qrt2, Qrt3, Qrt4 so I make another 4 lists, 1st one, Jan,Feb,Mar and named it as Qrt1 so on. Can I have 3 comboboxes in a userform so that 1st one shows Qrt1,Qrt2, Qrt3, Qrt4 and when Qrt3 is selected the 2nd combobox populate with the list Jul,Aug,Sep and when Aug is selected the 3rd combobox populate with the list named Aug. A sample code is very much appreciated. Cecil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo boxes
Cecil,
Try the following code in the form's code module. It assumes you have three comboboxes named cbxQuarter, cbxMonth, and cbxData. Private Sub cbxQuarter_Change() With Me.cbxMonth .Clear Select Case Me.cbxQuarter.ListIndex Case 0 .AddItem "Jan" .AddItem "Feb" .AddItem "Mar" Case 1 .AddItem "Apr" .AddItem "May" .AddItem "Jun" Case 2 .AddItem "Jul" .AddItem "Aug" .AddItem "Sep" Case 3 .AddItem "Oct" .AddItem "Nov" .AddItem "Dec" End Select .ListIndex = 0 End With End Sub Private Sub cbxMonth_Change() Dim Rng As Range With Me.cbxData .Clear For Each Rng In Range(Me.cbxMonth.Value) .AddItem Rng.Text Next Rng .ListIndex = 0 End With End Sub Private Sub UserForm_Initialize() With Me.cbxQuarter .Clear .AddItem "Qtr 1" .AddItem "Qtr 2" .AddItem "Qtr 3" .AddItem "Qtr 4" .ListIndex = 0 End With Me.cbxMonth.ListIndex = 0 End Sub -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Cecilkumara Fernando" wrote in message ... Hi All, I have 12 named ranges say Jan,Feb,.........Dec these 12 ranges falls in 4 categories say Qrt1, Qrt2, Qrt3, Qrt4 so I make another 4 lists, 1st one, Jan,Feb,Mar and named it as Qrt1 so on. Can I have 3 comboboxes in a userform so that 1st one shows Qrt1,Qrt2, Qrt3, Qrt4 and when Qrt3 is selected the 2nd combobox populate with the list Jul,Aug,Sep and when Aug is selected the 3rd combobox populate with the list named Aug. A sample code is very much appreciated. Cecil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Populating combo boxes
Thanks both of you Tom & Chip
Regards, Cecil "Cecilkumara Fernando" wrote in message ... Hi All, I have 12 named ranges say Jan,Feb,.........Dec these 12 ranges falls in 4 categories say Qrt1, Qrt2, Qrt3, Qrt4 so I make another 4 lists, 1st one, Jan,Feb,Mar and named it as Qrt1 so on. Can I have 3 comboboxes in a userform so that 1st one shows Qrt1,Qrt2, Qrt3, Qrt4 and when Qrt3 is selected the 2nd combobox populate with the list Jul,Aug,Sep and when Aug is selected the 3rd combobox populate with the list named Aug. A sample code is very much appreciated. Cecil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Combo boxes to change options based on other Combo boxes. | New Users to Excel | |||
Selecting subsets using combo boxes or list boxes | Excel Discussion (Misc queries) | |||
Questions on combo boxes and list boxes. | New Users to Excel | |||
Populating combo box with a function | Excel Programming | |||
Populating Combo Boxes | Excel Programming |