?
Solved

Excel VBA - Collection of checkboxes and labels on UserForm

Posted on 2016-11-07
8
Medium Priority
?
253 Views
Last Modified: 2016-11-16
I have a set of Checkboxes and related Labels on a UserForm.
A question on Collections
I have named them chk1AM, chk2AM etc. and (about to) name labels next to them as lbl1AM, lbl2AM etc.

I'm wondering if it would be a better strategy to use Collections, because I need to do the same for each pair and so (hopefully) only have to write a single block of code referenced by 1 to 7 (days of week) and chk or lbl.

I'm learning about collections from here
http://excelmacromastery.com/excel-vba-collections/#A_Quick_Guide_to_Collections

So what I'm looking for is some direction on strategy.

eg. do I continue to manually name the controls? or some way of looping through 1 to 7 to 'create' them?
How would I reference them to code against them?

Thanks for any advice.
0
Comment
Question by:hindersaliva
  • 5
  • 3
8 Comments
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41877165
What are you planning on storing in the collection and how will the collection be used?
0
 

Author Comment

by:hindersaliva
ID: 41877189
The checkboxes will be TRUE/FALSE to indicate the normal attendance days of an individual. They are FALSE by default. The labels next to each indicate a 'role' (text. Blank by default), which is selected from the combobox at the top.

So when the user turns on a checkbox the combobox a the top becomes visible. When a selection is made the selected item (text) is entered as text into the relevant label. That's the basic functionality I'm trying to achieve.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41877202
Can you attach your workbook?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hindersaliva
ID: 41877332
Here it is, Martin. Thanks.

The form is populated from an ADO recordset. The database is updated from the checkboxes and labels when Save is clicked. I can do both those.
MultiSelect-form-EE.xlsm
0
 
LVL 50

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41877443
I don't see how you could use a collection, but try this modification of your workbook. When the userform shows up (by double-clicking column "O", check a few checkboxes and then click the "Save" button. The changes that I made in the code are marked with 'new.
28981451.xlsm
0
 

Author Comment

by:hindersaliva
ID: 41877485
I was hoping to refer to a control by a Name and a Number, such as chkBooking(4) and lblRole(4) to refer to a checkbox and label pair. But your code helps. I can refer to them by a Number (looping through all controls) as I have named them with a Number in its name.

Thanks
0
 
LVL 50

Assisted Solution

by:Martin Liss
Martin Liss earned 2000 total points
ID: 41877505
You could name your controls chkBooking4 and lblRole4, etc.
0
 
LVL 50

Expert Comment

by:Martin Liss
ID: 41889963
I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0

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.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

864 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