[VBA Excel] Loop through CheckBox?
Solved
Hello everyone!
So I have a little problem with the CheckBoxes. I created a UserForm in Excel with 52 CheckBoxes numbered from 1 to 52 corresponding to the weeks (CheckBox1 -> W01, ..., CheckBox52 -> W52), and I added a "Select All" button to select them all automatically. My problem is as follows: I can't program a loop for the CheckBoxes to make it work... I tried the following code:
Dim i As Integer
For i = 0 To 52
CheckBox.i.Value = True
Next i
but it doesn't work. I even tried using brackets, parentheses, or braces, but nothing does it... (CheckBox[i], CheckBox(i) ...). It gives me an error message every time.
I would like to ask you if you know how to create a loop with the CheckBoxes, knowing that this "Select All" button is only the first (and simplest) of my problems...
Thank you very much!
Salim
So I have a little problem with the CheckBoxes. I created a UserForm in Excel with 52 CheckBoxes numbered from 1 to 52 corresponding to the weeks (CheckBox1 -> W01, ..., CheckBox52 -> W52), and I added a "Select All" button to select them all automatically. My problem is as follows: I can't program a loop for the CheckBoxes to make it work... I tried the following code:
Dim i As Integer
For i = 0 To 52
CheckBox.i.Value = True
Next i
but it doesn't work. I even tried using brackets, parentheses, or braces, but nothing does it... (CheckBox[i], CheckBox(i) ...). It gives me an error message every time.
I would like to ask you if you know how to create a loop with the CheckBoxes, knowing that this "Select All" button is only the first (and simplest) of my problems...
Thank you very much!
Salim
Configuration: Windows XP Internet Explorer 6.0 Office 2003
22 answers
-
Hello,
Yes, of course! Excel (rather VBA) does not have a collection as such! Unlike VB...
All the problems you encounter come from that! It is therefore impossible to assign a number in a collection of a particular type of object. The only collection it knows (Excel) is Userform1... All objects in a Userform are in a single collection (regardless of type)... Thus, checkboxes or labels and others are indexed one after the other without distinction.
The "trick" to navigate within a single type of object is to use, for example, the beginning of the generic name. For example, to set all the checked CheckBoxes (TRUE), you just need to check if the beginning of the name starts with CheckBox, and then go through each object...
Specifically, put a few CheckBoxes in Userform1 and a button.
Behind the button, put the following code:
=
Private Sub CommandButton1_Click()
Dim ole1 As Control
For Each ole1 In UserForm1.Controls
If Left$(ole1.Name, 8) = "CheckBox" Then ole1.Value = True
Next
End Sub
=
And when you click the button, all the checkboxes will be checked... Of course, the principle can be used for other objects...
HERE I use the beginning of the Name property of the object, but another property could be used. GroupName is made for that...
--
See you!
Best regards, Us.-
-
@sncfYes indeed...
I was a bit radical in saying that there was only the userform... In fact, we can also use the FRAME which also has the CONTROLS collection... This way we can browse just inside it... Which is even more elegant... With: For Each ole1 In Frame1.Controls
Finally, you will have understood the principle, I think...
--
Best regards, Us.
-
-
Hello everyone,
I'm taking advantage of the topic "checkbox" to try to find a solution to my problem:
One of my UserForms contains 2 editable Lists ("ComboBox1 & ComboBox2"), when the user selects a customer group in ComboBox1, only customers belonging to the selected group in ComboBox1 appear in ComboBox2. ( classic until now )
Below these two ComboBoxes, I want to display (and hide) "CheckBoxes" corresponding to the customers listed in ComboBox2, to allow the user to choose which customers they want to display.
Example: ComboBox1.Value = Group Axson
==> ComboBox2 = {Axson Fr, Axson All, Axson Bel, Axson Esp...}
CheckBox1: Axson Fr
CheckBox2: Axson All
CheckBox3: Axson Bel
.
.
.
.
.
.
.
You get the idea? Is this possible by defining, for example, 10 checkBoxes that I know how to show and hide in the UserForm but by setting the "Caption" property as a variable as well as possibly other properties....
Thank you for your help -
Hi,
Weird behavior, indeed. What happens if you click just once and wait for a moment? Because logically (if we can say that) clicking multiple times shouldn't have more effect than clicking once if you’re using the "click" event...
Unless you're using (maybe another possibility) the "GetFocus" property and other "Focus thing," which would mean that the first click only puts the focus on the userform before the second one actually validates the click on the button. But this can be easily seen by looking at whether the userform you click on has an active bar or not...
Otherwise, to post a message, just click on "Programming Forum" right below these messages, then on the new page (the general page), choose the link "New message"... and the rest follows...
--
See you,
Best regards, Us.-
Hello Us,
Well, it's not that bad; it can stay like that for now. After all, it's not for me that I made it (just kidding).
Thank you for the guidance on the topics. I will be undertaking a -small- creative project in the coming weeks, and I will certainly need all the resources available on VBA (for example -if I can't find it myself- how to launch mini videos from a CheckBox that come from a tutorial recording what's happening on the screen to deliver it in a training format).
Anyway, a big thank you, and don't hesitate to reach out if needed.
Phil
Helping others is helping oneself.
-
-
In the close button code or the button of your second userform,
'to uncheck the CheckBox UserForm1.CheckBox1.Value = False 'to disable (gray out) the CheckBox UserForm1.CheckBox1.Enabled = False
-
Hello everyone,
A big thank you, it was quite simple but it still needed to be thought of!
Another little "hitch" in my programming:
- when I click on the CommandButton meant to clear the ComboBox it's placed on, one click is not enough; I need two or even three clicks for it to work.
Is this avoidable? I think so, but I hesitate to add to my macro (afraid of overloading it).
Thanks in advance. -
@PhilippeHi,
Under which event did you place your code?
If you are using "CommandButton1_DblClick", it requires 2 clicks for the code to run... (which is normal)
Normally (most often), we use a single click with the event "CommandButton1_Click"
This might be the source of your problem...
Example:
Private Sub CommandButton1_Click() MsgBox "one click" End Sub Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) MsgBox "a double click!" End Sub
--
See you later
Sincerely, Us. -
@usGood evening Us,
Thank you for your post. I checked (just in case) but I didn't request the DblClick to validate.
My problem seems more like a bug rather than an inadvertent command insertion. Originally, my little program was working perfectly. This issue only appeared after I added the codes to disable the checkboxes.
A detail that may be significant is that the call for certain userforms (which contain the help for my little program) occurs on different comboboxes. Let me explain as I have difficulty making myself understood (we're off to a good start lol); I will be using the default names on purpose to keep it simple:
Userform1 contains: 1 label (explanation); 3 Checkboxes (which condition for 2 of them the opening of an error combobox (which, when closed, unchecks and disables the Checkbox that called it) and for the last one, normal operation (the box activates when selected); 2 CommandButtons (one for OK (which closes Userform1 and opens Userform2) and the other for HELP (which opens the help Userform);
It is at the level of the two error Comboboxes (as they are different) that my issue lies, I have to click 2 or even 3 times on the CommandButton (named OK - I lacked imagination) to return to the original Userform.
I don't know if all this is understandable, in case I can always detail the obscure aspects of my explanation.
In any case, thank you very much for your help, I appreciate it and hope to be able to offer mine in turn soon.
Phil
P.S.: Where do I need to go to create a topic?
-
-
Hello everyone,
This is my first post here
I have a silly little question (I hope it hasn't been answered elsewhere)
I created checkboxes on a userform and scripts that execute when they are activated. For example, another userform opens indicating that the user made a mistake by activating this checkbox.
I would like the checkbox that was mistakenly activated to automatically turn off when closing this new userform.
Is there any code for that?
Thank you in advance, you would be doing me a HUGE favor. -
to close a form named userform1
unload userform1
--
Pascal -
USERFORM1.SHOW = Load and display the userform1
USERFORM1.LOAD = Load the userform1 (but do not display it)
USERFORM1.HIDE = Hide the userform1 (it remains in memory as long as it is loaded)
UNLOAD USERFORM1 = Unload Userform1! So it closes the userform (it is no longer visible)
In general, we use this last one behind a button where we code:
Unload Me
Here is some vocabulary... :-);
--
Best regards, Us. -
Thank you so much, guys!!
I opted for the SNCF solution, which works very well, because I was already committed to this path, but the Us solution seems very clever too.
In any case, I managed to do what I wanted, thank you all! ;)
Two tiny questions for the road:
1. How do I call the Userform from the macros?
2. When clicking OK, the Userform will generate the program I want, but I would like the Userform to close automatically right after. How can I do that?
Salim
EDIT: I found the solution for the first question ;) I went to the modules, I typed:
Sub UserForm()
UserForm1.Show
End Sub
and it created a macro to display my Userform
EDIT again: Similarly, I found the solution for the second question (I’m really on fire today lol). This time, you need to put at the end of the Private Sub of the Validate button: Userform1.Hide... Simply ;)
Thanks again for your kindness, your expertise, and your patience :)
See you soon
Salim-
-
@usYes, yes I saw it :)
I definitely prefer Unload to Hide, but I’m keeping my Show, I like it a lot lol
Thank you for these very informative details :)
Salim -
@usYes, most of the time we use SHOW and UNLOAD! But as soon as you use multiple USERFORMS, you'll find it more elegant to just hide the Userforms; it’s more advantageous (actually easier to code)... Indeed, this way you can still read the entered data without having to put them into auxiliary variables... There you go, see you later!
--
Best regards, Us. -
@salim2103Salim I agree with us
To close the form, you need to be sure that we no longer need the data contained in it; otherwise, it's a hassle
--
Pascal
-
-
I was just about to write to you that the second solution didn’t work... It would have been quicker... Too bad!
I'll focus on the first one, which has to work for sure ;) -
solutions to solve your problem
Solution 1
Removal of the label Label1 (actually I just moved it under S02)
Resizing of Checkbox1 (it was hiding everything you could write in caption)
Caption = S01
Alignment = 0 –fmAlignmentLeft
TextAlign = fmTextAlignRight
By slightly resizing it in height, you achieve the desired result and also eliminate half of the controls (all the Labels)
-
It's sent ;)
The purpose of this Userform is to simplify the use of a VBA program. The user will be able to choose the weeks they want on the Userform, and it will take care of running the programs for those weeks. The Select All button is just the beginning, as I mentioned in my first post. -
To clarify things, could you send me your form (export CTRL+E to generate a frm file)? I’ll send you a private message to let you know my email address.
-
To be clear, each week i is associated with a CheckBox numbered i (CheckBox25 for week 25, for example), and each CheckBox i is accompanied by a Label with the same number i (Label25 for CheckBox25, which is labeled "S25").
So week 25 is indicated by a checkbox right next to a label "S25". Of course, the Labels are also part of the Frame...
I hope that's clear :) If not, feel free to ask me. -
What is the purpose of putting a label to indicate S01 when in the properties of the checkbox you have a caption that allows you to do it (plus it fits half as many controls)?
-
-
@salim2103OK, but the "Caption" property of the checkbox allows you to do just that (it's actually a label integrated into the checkbox), hence my question.
Why not use this property for each checkbox and eliminate all these labels that have become unnecessary? -
@sncfI modified the Caption property but the name does not appear on the Userform. Is that normal??
-
@salim2103To clarify, could you send me your form (export it from the file menu of the Visual Basic editor (CTRL+E) to generate a frm file)? I'll send you a private message to provide you with my email address.
-
-
I don't understand, what is the name of your first checkbox and what is the name of your first label and what is the label
Are the labels also in frame1? -
Aïe ! I have a little problem again... On my Userform, there are not only CheckBoxes, but also Labels (Headings) to indicate which week corresponds to each checkbox... These labels are numbered in the same way as the CheckBoxes. So when I run your program, it changes the name of the Labels (the Labels go from "S01", "S02", ... to "True").
What a hassle!! lol
I tried to move the Labels out of the Frame, but the Frame always stays in the foreground... And I also tried to use Userform1.Frame1(i).Value = True, but it gives me an error message... -
Awesome!! Thank you so much, SNCF ;) It seems to be working.
I'm going to try that right away on my project and see if it can resolve my issues. I'll get back to you as soon as the tests are done.
Thanks again! :) -
It's still not working... I think it comes from the "Me" which I don't understand the meaning of.
Also, the response to xjl interests me a lot as well.
Thanks in advance ;) -
I'm sorry, I interrupt, but does anyone know why this code doesn't work:
For Each CheckBox In Sheets(1).CheckBoxes
CheckBox.Value = True
Next CheckBox
I feel like it's not finding the CheckBox in the collection and I don't understand why, but if someone manages to make this code work, it would solve the problem... -
I apologize
here is the correct code
Dim i As Integer
For i = 0 To 51
Me.Frame1(i) = True
Next i
- 1
- 2
Next