Excel Checkbox Dilemna

March 4, 2010 9:22 AM

Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range. This has driven me crazy all day today.

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.

I am a total macro newbie so please be gentle.

posted by kev23f to Computers & Internet (5 answers total)

Best answer: Without being able to help directly... One way to get an idea of the macro you need to write is to record a macro doing what you want to do and then look at the code of the recorded macro. This might give you a nice starting point.
posted by PaulZ at 9:47 AM on March 4, 2010

Response by poster: aha, yep, I tried doing that, and the macro wouldnt record the "unclick" action.
However, i just got it to record me doing a "delete FALSE value" from the relevant cells, which i hadnt thought of till now, and that seems to be working ok.

Maybe thats me sorted! But any other ideas gratefully accepted....

And thanks PaulZ!
posted by kev23f at 9:55 AM on March 4, 2010

I can't seem to access the cell link property of these checkboxes (which would allow you to do this by a range), so you'd probably have to know the specific names of the checkboxes in each range you're changing and just set them false one by one.

Sounds like setting the value of the cells, rather than the checkboxes, is the way to go.
posted by pompomtom at 2:57 PM on March 4, 2010

this isn't a macro, but have a look at the spreadsheet here. not a macro, but might give you something more to work with. good luck.
posted by xiaolongbao at 5:21 PM on March 4, 2010

Best answer: Quick update here, I was given the following code, which works perfectly. Incidentally, PaulZ's suggestion of just recording the steps 'manually' led me to a solution too, so best answer for him.

Sub Uncheck
Dim cb As Object, rng As Range
On Error Resume Next
Set rng = Range("A1:G1")
For Each cb In ActiveSheet.CheckBoxes
If Not Intersect(Range(cb.LinkedCell), rng) Is Nothing Then
cb.Value = False
End If
Next cb
End Sub
posted by kev23f at 12:06 AM on March 5, 2010