VBA Option Buttons

[ryancgarrett]

I'm fairly new to VBA and have never used option buttons before, but I'm positive what I want to accomplish is possible. I have a form with a blank listbox and 5 option buttons. When the user opens the form and selects an option I want the listbox to populate based on the option button selected.

In short, how do I use option buttons to define the rowsource for a listbox?

Thanks for your help!

 

[AlphaFrog]

Private Sub OptionButton1_Click()
     ListBox1.RowSource = "Sheet1!A1:A10"
End Sub

Private Sub OptionButton2_Click()
     ListBox1.RowSource = "Sheet1!C1:C10"
End Sub

Private Sub OptionButton3_Click()
     ListBox1.RowSource = "Sheet1!E1:E10"
End Sub

Private Sub OptionButton4_Click()
     ListBox1.RowSource = "Sheet1!G1:G10"
End Sub

Private Sub OptionButton5_Click()
     ListBox1.RowSource = "Sheet1!I1:I10"
End Sub

 

[ryancgarrett]

Hmmm that still isn't working. The theory sounds good, but it always gives me an error. I have tried the following:

optAsset.RowSource = "Chart of Accounts!B3:B17"
optAsset.RowSource = Sheets("Chart of Accounts").Range("B3:B17")
optAsset.RowSource = Sheets("Chart of Accounts").Range("B3:B17").Value

All of these have returned either a type mismatch error or a member not found error. Am I still doing something wrong?

 

[AlphaFrog]

If the sheet name has spaces in it, you need to surround the name with single quotes...

optAsset.RowSource = "[COLOR="Red"]'[/COLOR]Chart of Accounts[COLOR="Red"]'[/COLOR]!B3:B17"

 

[ryancgarrett]

That was it, perfect. Thanks for your help!

 

[AlphaFrog]

You're welcome.