ComboBox List properties

[GiraffetheGeek]

Hi there,

I am new to programming excel with VBA, slowly picking up on things but I must be having a blonde moment at the moment.

I have two combobox's, RCComboBox and SDComboBox.

Also Cells AI46 to AI58 are number 1-13 and then the cells beside each one have two dates. i.e AI46 is 1 and then AJ46 is date1 and AK46 is date2 etc.

RCComboBox has it's ListFillRange set as AI46:AI58 (users can only pick 1-13)

I want SDComboBox's ListFillRange to be set to the range of the two cells beside based on the value in RCComboBox. ie if the user selects 2 in RCComboBox then SDComboBox's ListFillRange should be AJ47:AK47.

I have started with:

Private Sub RCComboBox_Change()
If RCComcoBox.Value = 1 Then SDComboBox.ListFillRange = AJ46:AK46
If RCComcoBox.Value = 2 Then SDComboBox.ListFillRange = AJ47:AK47

etc.

But it doesn't seem to work and I am now stumped.

Any ideas would be greatly appreciated.

 

[GTO]

Hi there,

I do not often use controls on sheets, save the occassional button, so was unfamiliar with ListFillRange. It appears it wants an address string, so I think you just need the quote marks.

Me.ListBox1.ListFillRange = "A1:A5"
Me.ComboBox1.ListFillRange = "A1:A5"

 

[GiraffetheGeek]

Thats helped a little - also noted I had misspelled and had written RCComcoBox not RCComboBox.

So now my code is:

If RCComboBox.Text = "1" Then SDComboBox.ListFillRange = "AJ46:AK46"
If RCComboBox.Text = "2" Then SDComboBox.ListFillRange = "AJ47:AK47"
If RCComboBox.Text = "3" Then SDComboBox.ListFillRange = "AJ48:AK48"
If RCComboBox.Text = "4" Then SDComboBox.ListFillRange = "AJ49:AK49"
SDComboBox.ListFillRange = "AJ58:AK58"

My intention being that if either 1,2,3 or 4 populate RCComboBox then the appropriate ListFillRange is entered, otherwise the ListFillRange of AJ58:AK58 is entered. (I will extend for values 5-13 once I have the code working)

However, no matter what RCComboBox's value is SDComboBox's ListFillRange is AJ58:AK58. Secondly when using the SDComboBox only 1 value is there to select when the range AJ58:AK58 has two values.

 

[Norie]

Do you mean you want to populate the 2nd combobox with all the dates between the 2 dates in column AJ and AK of the row of the item selected in the 1st combobox.

eg if AI46=1, AJ46=12/05/2011, AK46 =30/05/2011 then populate the 2nd combobox with 12/05/2011, 13/05/2011,...,30/05/2011

 

[GiraffetheGeek]

I have data in 3 columns.

......AI.... AJ.......... AK
46....1... Date1..... Date2
47 ...2 ...Date3 .....Date4
48 ...3... Date5......Date6
49 ...4... Date7..... Date8
50 ...5 ...Date9 .....Date10
51 ...6 ...Date11 ....Date12
52 ...7 ...Date13 ....Date14
53 ...8 ...Date15 ....Date16
54 ...9 ...Date17 ....Date18
55 ..10 ..Date19 ....Date20
56 ..11 ..Date21 ....Date22
57 ..12 ..Date23 ....Date24
58 ..13.. Date25 ....Date26

So RCComboBox ListFillRange is AI46:AI58 (meaning user can pick from 1-13). I want SDComboBox ListFillRange to change depending on what the user picks in RCCombobox. ie if user selects 3 in RCComboBox then I want SDComboBox's available options to be cells AJ48 and AK48, or in otherwords if they select 3 in RCComboBox then they should ony be able to pick Date5 or Date6

Hope that makes sense

 

[Norie]

You just want to add those 2 dates?

Try this.

Option Explicit
 
Private Sub RCombobox_Change()
Dim rng As Range
 
    If RCombobox.ListIndex <> -1 Then
    
        SDCombobox.Clear
                
        ' get range of selected item in 1st listbox
        Set rng = Range(RCombobox.ListFillRange).Cells(RCombobox.ListIndex + 1)
        
        SDCombobox.AddItem rng.Offset(, 1).Text ' date 1
        
        SDCombobox.AddItem rng.Offset(, 2).Text ' date 2
        
        ' optional - selects date 1 in second combobox
        SDCombobox.ListIndex = 0
 
    End If
    
End Sub

 

[mikerickson]

Why not

Private Sub UserForm_Initialize()
    RCComboBox.RowSource = "AI46:AI58"
    With SDComboBox
        .ColumnCount = 2
        .RowSource = "AJ46:AK58"
    End With
End Sub

Private Sub ListBox1_Click()
    SDComboBox.ListIndex = RCComboBox.ListIndex
End Sub

 

[GiraffetheGeek]

Norie,

Tried your code, I get a "Run-time error '424': Object required"

When I go to debug the line "If RCComboBox.ListIndex <> -1 Then" is highlighted


mikerickson,

Just looking at your code and I am a little confused (I did say I was a beginner )

I don't have a list box on the spreadsheet so I don't follow the ListBox1_Click() code, sorry



I don't mind emailing the spreadsheet if that helps

 

[Norie]

Where do you have the controls?

Are they comboboxes or listboxes?

I assumed they were comboboxes on a worksheet that were created from the Control Toolbox toolbar.

One reason for that is that a listbox/combobox on a userform doesn't have a ListFillRange property.

 

[GiraffetheGeek]

Yeah I have the two combobox's on a sheet that have been created from the Control Toolbar.