Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

kev23f

New Member
Joined
Mar 4, 2010
Messages
4
So i have a row of 7 checkboxes, inserted using “Insert – Form Controls” dialog in excel 2007.
Each checkbox sits above a cell, and each checkbox has been formatted so that it has a “cell link” to the cell directly beneath it.
The checkboxes are not grouped or anything, they are all separate.
I have several rows of checkboxes similar to this.

I would like to create a macro that when run, will uncheck all the boxes that are within a certain range, for example, from A1 to G1.

I have found VBA that will clear all checkboxes on a sheet, but the key for me is to be able to specify which checkboxes get cleared when i run the macro.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If you do have cell links all you should need to do is change the value in the linked cell, which should be pretty straightforward.
Code:
Range("A1:G1").Value = ""
Not 100% sure if "" is the right thing to use - I'm testing it at the moment.:)

I'll post back.
 
Upvote 0
By the way, I'm totally new to vba and macro's, please be as explicit as possible in any suggestion, if that's ok? For example, what exactly should i do with the code snippet above?
 
Upvote 0
Could you be as explicit as you can about what you are actually trying to achieve?

You originally mentioned a named range then you refer to A1:G1.

Also you seem to be referring to multiple rows of multiple checkboxes.

That might not be a good idea.:)

The more checkboxes you have the harder it's going to be to deal with things, and it might also increase the size of the file.

Perhaps there's another approach you could take to do what you want.
 
Upvote 0
apologies, i see how my "named range" confuses matters. Basically, i want to assign a macro to a button, that when pressed, clears all the ticks from the boxes that are sitting on top of cells A1 to G1.
 
Upvote 0
Hi, Try this:-
Alter Range "Rng" to suit.
Code:
[COLOR="Navy"]Sub[/COLOR] MG04Mar32
[COLOR="Navy"]Dim[/COLOR] cb [COLOR="Navy"]As[/COLOR] Object, rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]On[/COLOR] [COLOR="Navy"]Error[/COLOR] [COLOR="Navy"]Resume[/COLOR] [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] rng = Range("A2:A4")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] cb [COLOR="Navy"]In[/COLOR] ActiveSheet.CheckBoxes
    [COLOR="Navy"]If[/COLOR] Not Intersect(Range(cb.LinkedCell), rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        cb.Value = False
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] cb
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Just add a button from the Forms toolbar like you added the checkboxes.

Right click it, select Assign macro..., and press the New button.

You should then see something like this.
Code:
Sub Button8_Click()

End Sub
That's where the code I posted should go.

Mind you I was sort of serious about the multiple checkboxes thing and I've got a feeling there's more to whatever you want to do.:)
 
Upvote 0
Quick update here. The code posted by MickG works perfectly, thank you very much. Thanks also to Norie for clarifying re the button/code placement.
 
Upvote 0
@MickG,

Hello Mick,

I have tried your Code as in Post#6, and it does not clear the CheckBoxes in the specified range only, it does the whole Sheet.:(

In my situation I have Forms Control as well as Active-X Control ChechBoxes in Column H. Range H2:H10.

How would one adjust your Code to uncheck all of the CheckBoxes in Coulumn H only?

Thank you in advance.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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