Combobox range assignment doesn't change

[artz]

I am using optionbuttons to change the assignment of Combobox's range between two columns.

This code works fine to select the range for only one column:

Private Sub ComboBox_Set_Rng()
If ComboBox1.Enabled = True Then
    With ActiveSheet.Shapes("Combobox1")
       .OLEFormat.Object.ListFillRange = "A2:A20"
       End With
    End If
End Sub

When I added If statements selecting either of the two lists in different columns, the code always selects column B's range:

Private Sub ComboBox_Set_Rng()
If ComboBox1.Enabled = True Then

If OptionButton1 = True Then
With ActiveSheet.Shapes("Combobox1")
    .OLEFormat.Object.ListFillRange = "A2:A20"
End With
Else
If OptionButton2 = True Then
With ActiveSheet.Shapes("Combobox1")
    .OLEFormat.Object.ListFillRange = "B2:B20"
End With
End If
End If
End If
End Sub

Does anyone in the forum see what I am doing wrong?

Thanks,

Art

 

[lrobbo314]

I am assuming that these are controls added onto the worksheet, and if that is true, I've tested this code to toggle from the data in column A to column B given which optionbutton is selected.

Private Sub OptionButton1_Click()
ComboBox1.Clear
Dim i As Integer
i = 2
If OptionButton1.Enabled = True Then
    Do Until i = 21
        ComboBox1.AddItem Cells(i, 1)
    i = i + 1
    Loop
End If
End Sub

Private Sub OptionButton2_Click()
ComboBox1.Clear
Dim i As Integer
i = 2
If OptionButton1.Enabled = True Then
    Do Until i = 21
        ComboBox1.AddItem Cells(i, 2)
    i = i + 1
    Loop
End If
End Sub

 

[BrianB]

Artz. For what it's worth your code works correctly in XL2000
Using controls from the Controls toolbox

The only thing I can suggest is that you fully qualify .Value as in

If OptionButton1.Value = True Then

Strangely Combobox1.Clear does not work for me in this context.

 

[artz]

lrobbo314,

Thanks for your response. With your code, I receive an unspecified Run Time Error at: ComboBox1.Clear in your code.

Don't know what's wrong??

Brian,

Thanks for your suggestion- doesn't hurt to overspecify, however, it didn't work. I am using XL2003.

Thanks again,

Art

 

[lrobbo314]

What I did was in XL2007, don't know if that makes a difference. I added 3 activex controls. 2 optionbuttons and a combobox. optionbutton1, optionbutton2, and combobox1. Then in the VBA editor, I go to the properties of each optionbutton.click event and add the code I posted.

Maybe try and go step by step like that, adding the controls over again. Hopefully that would work, otherwise, I don't really know why you would be getting a runtime error.

 

[artz]

I created a worksheet from scratch as you suggested and your code works fine. - Thanks, Art

 

[artz]

Oh, I just realized one more thing. Hope that you can help with this. When I was checking out your code I missed this.

The two ranges for the comboboxes are on different sheets. Can your code be modified to set ranges on different sheets?

Thanks,

Art

 

[lrobbo314]

Yeah easily. Just put the sheet in front of the part of the code that says "cells(i,2)". so if your sheet with the data is named sheet2, then you line of code will read sheet2.cells(i,2)

 

[artz]

That works fine! Thanks a lot. –Art