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

[kev23f]

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.

 

[MickG]

Possibly something like this:-

[COLOR="Navy"]Sub[/COLOR] MG26Jun45
[COLOR="Navy"]Dim[/COLOR] ctrl [COLOR="Navy"]As[/COLOR] OLEObject, Rng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Cb [COLOR="Navy"]As[/COLOR] CheckBox
[COLOR="Navy"]Set[/COLOR] Rng = Range("H2:H10")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] ctrl [COLOR="Navy"]In[/COLOR] ActiveSheet.OLEObjects
    [COLOR="Navy"]If[/COLOR] TypeName(ctrl.Object) = "CheckBox" And Not Intersect(ctrl.TopLeftCell, Rng) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
        ctrl.Object.Value = False
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Cb [COLOR="Navy"]In[/COLOR] ActiveSheet.CheckBoxes
    [COLOR="Navy"]If[/COLOR] Not Intersect(Cb.TopLeftCell, 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

 

[Legacy 167133]

Awesome MickG,

You are a Scholar and a Gentleman, outstandingly knowledgeable!!!ray:

Thank you sooo much!

Kind greetings!

 

[MickG]

You're welcome