Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

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
0
DavidH7470
Asked:
DavidH7470
  • 7
  • 5
  • 4
2 Solutions
 
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
 
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
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now