Solved

Excel VBA - Collection of checkboxes and labels on UserForm

Posted on 2016-11-07
8
81 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 47

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 47

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 47

Accepted Solution

by:
Martin Liss earned 500 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 47

Assisted Solution

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

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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

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…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

685 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