Userform Checkboxes - Select All/Unselect

Status
Not open for further replies.

jroo

Board Regular
Joined
May 22, 2003
Messages
157
Hi - I'm creating a userform where I'd like to have about 10 checkboxes. How would I go about creating another check box that would select/unselect those 10 boxes?

I tried googling it and came across this piece of code below, but it only selects all. It doesn't allow me to unselect all. Please help. Thanks


Private Sub SelectAllCheckBox_Click()

Dim oCtrl As Control
For Each oCtrl In Me.Controls


If TypeOf oCtrl Is msforms.CheckBox Then
oCtrl.Value = True
End If
Next

End Sub
 
You will need to explain more about exactly what you want to happen.
And you should only use my code.
Tell me the name of the Checkbox you want the code in.
You do not need to enter my code in more the one checkbox.

And no code is needed in any of the other Check Boxes.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
I agree that a full explaination of what you are trying to achieve is needed.
The first post where you resurected this 8 year old thread isn't clear if you want a "Un/Check All" checkbox to apply to only some check boxes or all checkboxes (as My Answer Is's code handles).
If there are only some checkboxes to be handled, are they all in the same Group?
Are there any in the Group not to be controlled by the master?
Are the Master and the Sub checkboxes in the same Frame/MultiPage?
Do you want it back informative, i.e. as you un/check the sub checkboxes, do you want the Master checkbox to be checked/unchecked/null depending on the mix of the group?
 
Last edited:
Upvote 0
I have 38 CheckBoxes. All checkboxes 1-38 are named CheckBox1, CheckBox2, CheckBox3, etc. Checkboxes 1-37 serve a function of hiding a specific column. Checkbox38 is the select/deselect all. Originally, I started with regular checkboxes directly on my sheets and everything was perfect except for an intolerable glitch where when I sorted my data, it would cause my checkboxes to move such that the checkbox would be in the right spot but the place to click it would shift about an inch. So I created my checkboxes in a userform and copied my code over.

The purpose of the userform is to allow users to easily hide columns that they do not want to see. Since there are so many checkboxes, I want to add a select/ deselect all checkbox.
 
Upvote 0
And so did my code not work for you?
Put my code in CheckBox38
And the code would be:


Code:
[LEFT][COLOR=#333333][FONT=monospace]Private Sub CheckBox38_Click()
'Modified  10/7/2019  11:26:25 AM  EDT
For Each Control In Me.Controls
    If TypeName(Control) = "CheckBox" Then Control.Value = CheckBox38.Value
Next
End Sub[/FONT][/COLOR][/LEFT]

When you click on CheckBox38 the code runs.
 
Last edited:
Upvote 0
Its working now. I had to delete the checkboxes that I had and put new ones in because I think something was wrong with the names of the checkboxes.
Thank you!
 
Upvote 0
Nevermind.
So it was working, then I added control source references so that when I open and close the Userform, the checkmarks save. Now when I click the Select all button, Checkbox1 is the only one that changes
 
Upvote 0
What is a select all Button?
You said:

Now when I click the Select all button, Checkbox1 is the only one that changes

You said earlier you wanted the script to run when you selected TextBox 38

See we can only help you when you provide specific details.

And you earlier said:
The purpose of the userform is to allow users to easily hide columns that they do not want to see. Since there are so many checkboxes, I want to add a select/ deselect all checkbox.

I do not know why you would need a UserForm with 38 checkboxes to hide columns

 
Upvote 0
I apologize for being vague. By button, I meant CheckBox38. When I select and deselect (check/uncheck) CheckBox38, the only checkbox of the other 37 to be affected is CheckBox1.

For why I need a userform, I will not be the primary user of this spreadsheet. I am trying to make it as easy as possible for other users. Users are going to be constantly hiding columns they don't need to see. Can you think of a better way to hide columns in a user friendly way?

Do you know why adding a ControlSource to the checkboxboxes would cause th Select/Deselect all functionally to fail?
 
Upvote 0
I would think having a user double click on say Range(A1) to hide Column 1 would be a easy way.
But then unhiding would not work this way.


You said earlier you had 38 Checkboxes and you want to click Checkbox38 which then would select all the other 37 checkboxes.

But in your last post you said:
the only checkbox of the other 37 to be affected is CheckBox1.


So tell me what you wanted to happen.

Are you wanting a user to easily hide and unhide columns? Or just hide them.

And then you want to do something easy to unhide all columns. Is that what you want.
 
Upvote 0
What I would like:

A userform with 37 checkboxes that work as a toggle switch to hide and unhide columns. When the checkbox 1-37 is TRUE, then 'show column', when checkbox 1-37 is FALSE, then 'hide column. My current code is written to do that.
Then another checkbox (CheckBox38) that toggles all the 37 checkboxes to be checked or unchecked. When CheckBox38 is TRUE, then 'Show all columns', when CheckBox38 is FALSE, then 'Hide all columns'. OR When CheckBox38 is TRUE, then 'CheckBoxes 1-37 are TRUE', when CheckBox38 is FALSE, then 'CheckBoxes 1-37 are FALSE'

When the userform is closed and reopened, I should see the same checkbox configuration as the last time it was open.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,213,558
Messages
6,114,297
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top