PDA

View Full Version : Solved: Select/Unselect all check boxes macro help



livelike
03-27-2006, 04:42 PM
Hello Everyone, I think this is a rather easy macro question, but I?m not sure how to do it. :think: I have a template that allows user to select a couple of combo boxes (about 15 options) that results an amount added into a formula according to the box checked. I would like to assign 3 different buttons for options to 1) select all check boxes 2) unselect all and 3) invert selection. The tricky part is that I would like this to work regardless of the current status of each box (selected or unselected). Is this possible? Could someone help me please?

Thanks in advance!!!!

Jacob Hilderbrand
03-27-2006, 04:53 PM
Are these ActiveX controls on a worksheet?


Option Explicit

Sub CheckAll()

Dim CBox As OLEObject

For Each CBox In ActiveSheet.OLEObjects
If TypeName(CBox.Object) = "CheckBox" Then
CBox.Object.Value = True
End If
Next

End Sub


To uncheck just make the value False. To toggle replace this line:


CBox.Object.Value = True


With this line:


CBox.Object.Value = Not CBox.Object.Value

livelike
03-27-2006, 08:47 PM
Thanks DRJ, I tried your macro, but I could not get it to work. :dunno . I dont think I'm using any activeX stuff, but anyway, I'm uploading a sample file of what I have been working on. So if you could give it a look I would appreciated.
This is a basic example, users should only have to click on any of the combo boxes for desired options. All references would be hiden at the REF worksheet. I´m using vlookups link both worksheets, and automatically add the cost of the options.

Thanks!!!

Jacob Hilderbrand
03-28-2006, 08:50 AM
In that case we will simply need to change the values in the linked cell (that you have set for each check box).


Option Explicit

Sub CheckAll()

Dim CheckRange As Range

Set CheckRange = Range("T15,T17,T19:T20,T24:T25,T27:T28,T31,T33,T35,T37")
CheckRange.Value = True

Set CheckRange = Nothing

End Sub

Sub UnCheckAll()

Dim CheckRange As Range

Set CheckRange = Range("T15,T17,T19:T20,T24:T25,T27:T28,T31,T33,T35,T37")
CheckRange.Value = False

Set CheckRange = Nothing

End Sub

Sub Toggle()

Dim Cel As Range
Dim CheckRange As Range

Set CheckRange = Range("T15,T17,T19:T20,T24:T25,T27:T28,T31,T33,T35,T37")
For Each Cel In CheckRange
Cel.Value = Not Cel.Value
Next

Set Cel = Nothing
Set CheckRange = Nothing

End Sub

livelike
03-28-2006, 09:02 AM
Thanks a lot DRJ!!! It is exactly what I was trying to do. I'm relatively new to Visual Basic, Most of my macros are done with the recording option, and for some reason it did not record the check option in the boxes when I tried to do it. Probably the recording function does not "see" the checkboxes and has to be done manually. I just wasn't how to do it with programming.
:beerchug:

Jacob Hilderbrand
03-28-2006, 11:07 AM
Glad to help :beerchug:

The macro recorder is a good way to see how the code will be written (in some cases). It will add a lot of unnessesary code, and will also not record everything, but it is a start.