VB Optionbox Caption from a list in a Spreadsheet

I am looking for a code in VB that will populate a series of Option Box caption field with a list in a column in a spreadsheet.   I tried a Do loop to look down the cells in the spreadsheet and use a Dim variable to caption the Option Boxes but I was unsuccessful.  I prefer not to write  lines of code for each option box picking up the name in the spreadsheet cell as the caption if there is an easier way such as with a do loop using a variable and change the variable for each caption.

Thanks
DavidH7470Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

kgerbChief EngineerCommented:
DavidH7470,
I think this will do what you want.  It will change the caption of all the option buttons on a worksheet to whatever is in the cell directly to the left of the option button.  See the example workbook.  change the cells in column A and then click the "Change Captions" button.  The option button captions will automatically update.  Let me know if you need any help.

Kyle
Q-27316151-RevA.xlsm
0
DavidH7470Author Commented:
I dont think so.  I looked it over.  I forgot to explain that the optionboxes are on a form in Excel 2003 and when the form loads I wish to have the caption of the first optionbox to get it's caption from Cell a1 on sheet1, then the next option box to get its caption from A2 and so on.  While I can do it line by line I was hoping for shorter code to pick up the captions.  There are 40 in total and some will have no value in the spreadsheet so this option boxes would then not be visible when the form loads.

Thanks
0
kgerbChief EngineerCommented:
Try this

Kyle
Q-27316151-RevB.xlsm
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

kgerbChief EngineerCommented:
Gotta go home.  I'll try to check back to this later tonight.  Otherwise it will be tomorrow.

Kyle
0
NorieVBA ExpertCommented:
Do the option buttons already exist on the form?

PS You do mean a userform don't you?

Here's a workbook that will add option buttons to a userform taking the captions from the worksheet.


UFDynamicOptionBtnClassEx.xls
0
kgerbChief EngineerCommented:
DavidH7470,
Any luck?
0
DavidH7470Author Commented:
Hi Kyle.  No luck yet.  I thought maybe an example would help so I attached the following spreadsheet.  Please see the remark in the macro for what I am trying to accomplish.

Thanks

David Option-Button-Example.xls
0
NorieVBA ExpertCommented:
You can refer to controls using their name like this:
strOptName  = "OptButton"& StartRow-2
UserForm1.Controls(strOptName).Caption = Sheets("Sheet1").Cells(StartRow, 2).Value

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kgerbChief EngineerCommented:
Here you go
Sub LoadForm()
Dim i As Long
Dim StartRow As Long, EndRow As Long
StartRow = 3
EndRow = 6
For i = StartRow To EndRow
    UserForm1.Controls("OptButton" & i - (StartRow - 1)).Caption = Cells(i, 2)
Next i
UserForm1.Show
End Sub

Open in new window

Q-27316151-RevC.xlsm
0
NorieVBA ExpertCommented:
kgerb

Why not use the original loop?
0
kgerbChief EngineerCommented:
imnorie,
I guess I like For...Next loops when incrementing a variable so you don't have to do the i = i + 1.  No big deal, mostly personal preference I guess.

Kyle
0
NorieVBA ExpertCommented:
Kyle

Suppose it doesn't matter, just thought it might be used for something else as well.

PS You should add a worksheet reference for Cells.
0
kgerbChief EngineerCommented:
True, could get incorrect results if the right sheet isn't active.  good point.

kyle
Sub LoadForm()
Dim i As Long
Dim StartRow As Long, EndRow As Long
StartRow = 3
EndRow = 6
For i = StartRow To EndRow
    UserForm1.Controls("OptButton" & i - (StartRow - 1)).Caption = Sheets("Sheet1").Cells(i, 2)
Next i
UserForm1.Show
End Sub

Open in new window

0
NorieVBA ExpertCommented:
Yep, that's what I meant.

Been there myself.
0
DavidH7470Author Commented:
Thank you that worked great.  I now know something I didn't know yesterday.  
0
DavidH7470Author Commented:
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.