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.

 

[Norie]

Well that's how I tested the code I posted.

Where did you put the code?

It should go in the worksheet's module.

Actually if you double click the control you should see something like this:

Option Explicit

Private Sub RCCombobox Change()
 
End Sub

Which isn't what I saw because I'd named the combobox RCombobox, ie one C not two.

That could be why the code I posted didn't work - a typo.

 

[GiraffetheGeek]

Right so I fixed the type and it now looks like this

Private Sub RCComboBox_Change()
    Dim rng As Range
 
    If RCComboBox.ListIndex <> -1 Then
    
        SDComboBox.Clear
                
        ' get range of selected item in 1st listbox
        Set rng = Range(RCComboBox.ListFillRange).Cells(RCComboBox.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

Now I get "Runtime error '-2147467259 (80004005)': Unspecified error" and the line SDComboBox.Clear is highlighted.

Thanks heaps for your input so far - has been very much appreciated.

 

[Norie]

Have you set the ListFillRange property of SDComboBox?

If you have you need to remove that. ie just delete what you typed in it.

Unless that is you actually need it for some reason, but I can't see why when you are populating the combobox via code.

If that doesn't work post back.

PS I actually got a similar message because I'd mixed up the combobox's and was trying to add the dates to the wrong one.

 

[mikerickson]

Norie,

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

Sorry, I did my testing on a listbox instead of a combobox and missed the edit, that should be

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

The idea is that rather than setting the ListFillRange to one row
(the difference between a one row combobox and a label is ???)

instead of that, link the two comboboxes so that the same row is always selected from either.

 

[GiraffetheGeek]

@Norie

That worked! Thanks heaps.

Now I gotta get my head round what the code actually does but that can wait - thanks for your help