[VBA Excel] Loop through CheckBox?
Solved
salim2103
Posted messages
49
Registration date
Status
Membre
-
latuque -
latuque -
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 réponses
- 1
- 2
Suivant
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.
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.
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...
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...
Hello
Put all your checkboxes in a frame (frame1)
then try
for i = 0 to 51 (starting the numbering at 0 for the 1st control in the frame)
frame1(n)=true
next i
Put all your checkboxes in a frame (frame1)
then try
for i = 0 to 51 (starting the numbering at 0 for the 1st control in the frame)
frame1(n)=true
next i
Thank you SNCF for your quick response :)
I tried what you gave me, but all it does is check the 1st CheckBox (CheckBox1) only when I click the "Select all" button... So there is a problem.
When I looked more closely, I noticed that the "i" in the increment was not appearing in the command line, so I think it's normal that only one CheckBox is checked... What do you think?
Salim
I tried what you gave me, but all it does is check the 1st CheckBox (CheckBox1) only when I click the "Select all" button... So there is a problem.
When I looked more closely, I noticed that the "i" in the increment was not appearing in the command line, so I think it's normal that only one CheckBox is checked... What do you think?
Salim
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! :)
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! :)
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...
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...
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?
Are the labels also in frame1?
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)?
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.
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.
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.
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.
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.
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)
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)
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 ;)
I'll focus on the first one, which has to work for sure ;)
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
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
Yes, 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.
--
Best regards, Us.
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.
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.
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.
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.
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.
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.
Hi,
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:
--
See you later
Sincerely, Us.
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.
Good 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?
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?
- 1
- 2
Suivant
Thanks for the info (I'll keep that in mind, you never know).
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.