Checking checkboxes in a range

[Peltz]

Hi there
I manage tocheck or uncheck all checkboxes in my sheet. However, what I need to do is checking and unchecking all checkboxes in a range, eg B5:B17. Any help would be appreciated.

Thanks

 

[Peltz]

I' ve tried to bypass the problem by selecting spesific Checkboxes, but get the error: "Object variable or With Block variable not set"..

Sub Test()
Dim c As CheckBox
Dim i As Integer

For i = 1 To 18
If c.Name = ActiveSheet.CheckBoxes("Checkbox i").Name Then
        c.Value = False
End If
   
Next i
End Sub

I can't figure out whats wrong. Any input?

Thanks

 

[Trevor G]

Try changing this line:

ActiveSheet.CheckBoxes("Checkbox i")​

To

ActiveSheet.CheckBoxes("Checkbox" & i)​

I haven't tested

 

[Peltz]

No, sorry. Same error

 

[Peltz]

Sub MarkCheckBoxes()
Dim c As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each c In Ws.CheckBoxes
If c.Name = ActiveSheet.CheckBoxes("cb").Name Then
  c.Value = xlOff
  End If
Next c
End Sub

So... i tred to give the checkboxes thesame name... However it just keeps looping on the top (ist checkbox)..

 

[MickG]

Try this:-
NB:- These are forms "Checkboxes" not "ActiveX"

[COLOR="Navy"]Sub[/COLOR] MG23Nov09
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] CheckBox
[COLOR="Navy"]Dim[/COLOR] Ws [COLOR="Navy"]As[/COLOR] Worksheet
[COLOR="Navy"]Set[/COLOR] Ws = ActiveSheet
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] c [COLOR="Navy"]In[/COLOR] Ws.CheckBoxes
 [COLOR="Navy"]If[/COLOR] Not Intersect(c.TopLeftCell, Range("B5:B17")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
  c.Value = IIf(c =xlOn , xlOff, xlOn) '[COLOR="Green"][B][/B][/COLOR]
  [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] c

[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Peltz]

Hi

Thanks
It doesn't quite work. What happens is that the first checkbox in the range is toggeling. The other checkboxes are not affected.

 

[MickG]

All the text boxes must be set within the range("B5:B17"), are yours ???

 

[Peltz]

They are actually "B2:B11", but I changed the range when using your code in my worksheet

 

[MickG]

Have a look at this example:-
https://app.box.com/s/apaq7kdx948ln177pazlwuhrfsnwy4hv