Combobox range assignment doesn't change

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
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:
Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,037
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

Code:
If OptionButton1.Value = True Then

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

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791
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

Well-known Member
Joined
Jul 14, 2008
Messages
3,037
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Aug 11, 2002
Messages
791
I created a worksheet from scratch as you suggested and your code works fine. - Thanks, Art
 

artz

Well-known Member
Joined
Aug 11, 2002
Messages
791

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2008
Messages
3,037
Office Version
  1. 365
Platform
  1. Windows
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,125
Messages
5,599,856
Members
414,342
Latest member
K Darrell Smith

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top