Populating combo boxes

  • Thread starter Thread starter Cecilkumara Fernando
  • Start date Start date
C

Cecilkumara Fernando

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
 
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
 
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 (e-mail address removed)
 
Back
Top