show rowsource in comboboxes from two columns based on two options

0

Hi

I try  making  simple  userform  to  show  row source from two  columns  based on two  option .  so   first  when  run  the  userform should  be the  combobox1 =""    and if  I select  optionbutton1   then  should  fill combobox basd  on  column A  and  if  I select  optionbutton2   then  should  fill combobox basd  on  column B  . this  is  what  I  have .

Private Sub UserForm_Activate()
   ComboBox1.RowSource = ""
    If OptionButton1.Value = True And OptionButton2.Value = False Then
    ComboBox1.List = Sheets("SHEET1").Range("A2", Sheets("SHEET1").Range("A65536").End(xlUp)).Value
    ElseIf OptionButton2.Value = True And OptionButton1.Value = False Then
    ComboBox1.List = Sheets("SHEET1").Range("B2", Sheets("SHEET1").Range("B65536").End(xlUp)).Value
    End If
End Sub

but  this  doesn't work . any idea  to  fix  it ?

Answer
Discuss

Answers

0
Selected Answer

Hi there Mussa, 

There are a number of things to note:

  • Name your ranges and your range references will be SO MUCH easier for you to remember how to use.
  • The RowSource property and the List property fill the ComboBox in different ways and don't need to be used together.
  • You don't need to clear a ComboBox with the form loads, just don't fill the List property or RowSource property or use AddItem on that ComboBox and it will start life empty.
  • To do something in a UserForm when it loads, use the UserForm_Initialize event.
  • To do something when a specific option button is clicked, use the click even for that option button.
  • You don't need to check if a mutually exclusive option button is false and the current one is true - there can be only one (Highlander) (Too much Coffee this morning haha).
    • Use the GroupName property if you have multiple different sets of OptionButton controls.

UserForms are great, but there are lots of things involved with getting them working.

Here is sample code that should work for you.

Private Sub OptionButton1_Click()

ComboBox1.List = Worksheets("Sheet1").Range("A1", "A" & Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row).Value

End Sub

Private Sub OptionButton2_Click()

ComboBox1.List = Worksheets("Sheet1").Range("B1", "B" & Worksheets("Sheet1").Cells(Rows.Count, 2).End(xlUp).Row).Value

End Sub

I use column A and B for the combobox and the values begin in row 1.

Discuss

Discussion

actually   your  way  is  very  efficient  and  easy  . to  be  honest   I  thought  your  way  but  I  thought   it  doesn't  work  .it should  put  in userform active  to  show  the  data  . that  what  I  used to .  thanks  vry  much 
Mussa (rep: 16) Oct 13, '21 at 8:07 am
Add to Discussion
0

Mussa

In the attached workbook, I've changed your sub to:

Private Sub UserForm_Activate()

With Sheets("SHEET1")

    If OptionButton1.Value = True And OptionButton2.Value = False Then
        ComboBox1.List = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
        ElseIf OptionButton2.Value = True And OptionButton1.Value = False Then
        ComboBox1.List = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row).Value
    End If

End With

End Sub
(changes in bold- suggest you look again at Don's tutorial Get the Last Row using VBA in Excel). Strictly speaking you only need to Initialize like this if one of the buttons has its value pre-set to True in the UserForm. Otherwise you can delete that just use the code below (but will have no Combobox1 list until one of the option buttons is clicked).

To get the lists to toggle as you change the Option Buttons on the UserForm, there's also this code:

Private Sub OptionButton1_Click()
With Sheets("SHEET1")
    ComboBox1.List = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Value
End With
End Sub


Private Sub OptionButton2_Click()
With Sheets("SHEET1")
    ComboBox1.List = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row).Value
End With
End Sub
Both sets of code are in the attached file (but no Option Button is set to True as a default).

Hope this fixes things for you.

Discuss

Discussion

John 
thanks  for  your  answering, but  I  think  some things  need fixing .
first  in toggel  option button2  should  be  in  column B  not  A  .  you  repeat   twice in COLUMN A  for  two  OPTION BUTTONS  then option2  should  be  like  this 

With Sheets("SHEET1")
    ComboBox1.List = .Range("B2:B" & .Range("B" & Rows.Count).End(xlUp).Row).Value
End With 
and  in UserForm_Activate  ignores  column A   whether  select toggle option1 or  2 it  will  show the  column B
Mussa (rep: 16) Oct 13, '21 at 2:31 pm
Mussa.

Thanks for pointing out the error (I rushed my reply since I had to get somewhere). I've corrected my Answer and file.

Note that in the original file, I'd set OptionButton"s Value to True (so option2 was the default and was set on launching the UserForm). I've now removed that so intially you get a blank list for ComboBox1.
John_Ru (rep: 2147) Oct 13, '21 at 5:21 pm
(so option2 was the default and was set on launching the UserForm). I've now removed that so intially you get a blank list for ComboBox1.
I  don't  think  so    . I'm  confused 
Mussa (rep: 16) Oct 13, '21 at 5:46 pm
Mussa. It wss the default in the verrsion .... v0_a. xlsm file but now. Youve selected Don's answer so I assume that I don't need to do any more work on this. 
John_Ru (rep: 2147) Oct 14, '21 at 1:49 am
John 
I know  MR.Don   has  solved  my question . just  I  would  inform  you   how  your  answer  works .  that's  it.
thanks  for  your  time  .
Mussa (rep: 16) Oct 14, '21 at 4:17 am
Okay, thanks. 
John_Ru (rep: 2147) Oct 14, '21 at 6:08 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login