Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

VB Optionbox Caption from a list in a Spreadsheet

Posted on 2011-09-19
16
Medium Priority
?
305 Views
Last Modified: 2012-05-12
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
Comment
Question by:DavidH7470
  • 7
  • 5
  • 4
16 Comments
 
LVL 12

Expert Comment

by:kgerb
ID: 36563281
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
 

Author Comment

by:DavidH7470
ID: 36563385
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36563472
Try this

Kyle
Q-27316151-RevB.xlsm
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 12

Expert Comment

by:kgerb
ID: 36563486
Gotta go home.  I'll try to check back to this later tonight.  Otherwise it will be tomorrow.

Kyle
0
 
LVL 35

Expert Comment

by:Norie
ID: 36563531
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36566450
DavidH7470,
Any luck?
0
 

Author Comment

by:DavidH7470
ID: 36567437
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
 
LVL 35

Accepted Solution

by:
Norie earned 1000 total points
ID: 36567500
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
 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 1000 total points
ID: 36567557
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
 
LVL 35

Expert Comment

by:Norie
ID: 36567597
kgerb

Why not use the original loop?
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36567629
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
 
LVL 35

Expert Comment

by:Norie
ID: 36567678
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
 
LVL 12

Expert Comment

by:kgerb
ID: 36567697
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
 
LVL 35

Expert Comment

by:Norie
ID: 36567755
Yep, that's what I meant.

Been there myself.
0
 

Author Comment

by:DavidH7470
ID: 36567887
Thank you that worked great.  I now know something I didn't know yesterday.  
0
 

Author Closing Comment

by:DavidH7470
ID: 36567904
Thank you for your help.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question