Solved

VB Optionbox Caption from a list in a Spreadsheet

Posted on 2011-09-19
16
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 34

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 34

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
 
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 34

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 34

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 34

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

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 code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

617 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