select all - VBA

G

geversud

Board Regular

Joined

Jul 17, 2002

Messages

181

• Aug 9, 2002

• #2

Try this:

Private Sub CheckBox9_Click()
If CheckBox9.Value = True Then
CheckBox1.Value = True
CheckBox2.Value = True
...
CheckBox8.Value = True
Else
CheckBox1.Value = False
CheckBox2.Value = False
...
CheckBox8.Value = False
End If
End Sub

 

Upvote 0

Corticus

Well-known Member

Joined

Apr 30, 2002

Messages

1,579

• Aug 9, 2002

• #3

Try this,

Private Sub Checkbox_Click()

Dim i
For i = 1 To 8
CB = "CheckBox" & i
Me.Controls(CB).Object.Value = True
Next i

End Sub

Just name your Checkboxes Checkbox1, Checkbox2, etc. Or anything numbered 1-8 at the end.

HTH
Corticus
This message was edited by corticus on 2002-08-15 05:55

 

Upvote 0

Mark O'Brien

MrExcel MVP

Joined

Feb 15, 2002

Messages

3,530

• Aug 9, 2002

• #4

Or yet another suggestion where my "Select All" checkbox was named "CheckBox4":

<pre>
Private Sub CheckBox4_Change()
Dim ctrl As Control

For Each ctrl In Me.Controls

If TypeOf ctrl Is msforms.CheckBox Then
ctrl.Value = Me.CheckBox4.Value
End If
Next
End Sub</pre>

HTH

 

Upvote 0

M

Mudface

MrExcel MVP

Joined

Feb 18, 2002

Messages

3,339

• Aug 9, 2002

• #5

Mark- just out of interest, how would you do the same thing for a worksheet with a few checkboxes on it? I'd have thought you would iterate through the OleObjects collection, but I can't seem to make that work properly.

 

Upvote 0

L

Legacy 98055

Guest

• Aug 9, 2002

• #6

Hey Mudface. This might help...<pre>
Private Sub Worksheet_Activate()
Dim tb As OLEObject
With ActiveSheet
For Each tb In .OLEObjects
If tb.OLEType = 2 Then
.OLEObjects(tb.Name).Object.Text = ""
End If
Next
End With
End Sub</pre>

I think this is better for what you are doing.<pre>
Private Sub Worksheet_Activate()
Dim cb As OLEObject
With ActiveSheet
For Each cb In .OLEObjects
If InStr(cb.ProgId, "CheckBox")<> 0 Then
.OLEObjects(cb.Name).Object.Value = False
End If
Next
End With
End Sub</pre>
Tom
This message was edited by TsTom on 2002-08-09 14:09

 

Upvote 0

Mark O'Brien

MrExcel MVP

Joined

Feb 15, 2002

Messages

3,530

• Aug 9, 2002

• #7

Dammit, TsTom beat me to the punch , but here's how I would do it:

<pre>
Private Sub CheckBox5_Change()
Dim OLEctrl As OLEObject

For Each OLEctrl In OLEObjects
If OLEctrl.OLEType = 2 Then
OLEctrl.Object = CheckBox5.Object
End If
Next

End Sub</pre>

 

Upvote 0

M

Mudface

MrExcel MVP

Joined

Feb 18, 2002

Messages

3,339

• Aug 9, 2002

• #8

It certainly does, I was using

Code:

Private Sub CheckBox3_Click()
Dim ctl As OLEObject

For Each ctl In ActiveSheet.OLEObjects
    If TypeName(ctl) = "CheckBox" Then ctl.Object.Value = CheckBox3.Value
Next ctl
    
End Sub

and derivatives of it but this mod of yours works nicely:-

Code:

Private Sub CheckBox3_Change()
Dim tb As OLEObject
With ActiveSheet
    For Each tb In .OLEObjects
        If tb.OLEType = 2 Then
            .OLEObjects(tb.Name).Object.Value = .OLEObjects("Checkbox3").Object.Value
        End If
    Next
End With

End Sub

Thanks, Tom, nice one .

Edit: Way too slow, Chris .
This message was edited by Mudface on 2002-08-09 14:10

 

Upvote 0