Solved

VB Optionbox Caption from a list in a Spreadsheet

Posted on 2011-09-19
16
297 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
 
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 33

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 33

Accepted Solution

by:
Norie earned 250 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 12

Assisted Solution

by:kgerb
kgerb earned 250 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 33

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 33

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 33

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now