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]

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

Thanks for Your time.

When the code fires, it changes the status of all the checkboxes in the range. That is, checkboxes that are enabled are disabled, and vice a versa. What I need is for all the checkboxes in the range to be unchecked.

Again. Thank you so much.

 

[MickG]

Change line below:-

  c.Value = IIf(c = xlOn, xlOff, xlOn) '

To this line:-

c.Value = xlOff

 

[Peltz]

Works Perfect! Tank you so much. Saves me alot of work!


One more thing

Could you explin what the 'IIf' and -Checkbox.left = 70' command/part means. Thank you.

 

[MickG]

You're welcome
The "iif" statement is just to alter the Checkbox from the state its in "Checked/Unchecked" to the opposite State
and the "Checkbox1.left" was me just me tidying the checkboxes so they where all the same distance from the left hand side of the sheet. ( You can remove that Line).

 

[Peltz]

Great.

But, still have a problem:

These are now the codes im using to controll the checkboxes. I set the range A1:A10 for simplicity.

Sub Test()
Dim myCBX As CheckBox
Dim wks As Worksheet
Dim rngCB As Range
Dim strCap As String
Dim lCbx As Long
Dim i As Integer
Set wks = ActiveSheet
'''change this range if required
Set rngCB = wks.Range("A1:A10")
'''add caption text, if required
'strCap = "Test"
'lCbx = 1

For Each c In rngCB
  With c
    Set myCBX = wks.CheckBoxes.Add _
      (Top:=.Top, Width:=.Width, _
       Height:=.Height, Left:=.Left)
  End With
  With myCBX
    .Display3DShading = True
    .Name = "cb"
'''change linked cell offset, if required
''' Offet(rows, columns)
    .LinkedCell = c.Offset(0, 1) _
        .Address(external:=True)
    .Caption = "" 'strCap & lCbx
'''add name of macro to run, if required
'    .OnAction = ThisWorkbook.Name _
'        & "!mycbxMacro"
  'lCbx = lCbx + 1
  End With
Next c
End Sub

Private Sub CommandButton3_Click()
Dim c As CheckBox
Dim Ws As Worksheet
Set Ws = ActiveSheet
For Each c In Ws.CheckBoxes
 'c.Left = 70
 If Not Intersect(c.TopLeftCell, Range("A1:A10")) Is Nothing Then
  c.Value = xlOff
  End If
Next c
End Sub

For some reason your code does not disable the checkboxes.

 

[MickG]

In your first code you have Given each checkbox the same name, because of that when you loop through them (second code) the loop thinks they are all in cell "A1".

I have added a variable "n" to the first code as below and it now seems to work.

  .Display3DShading = True
    n = n + 1
    .Name = "cb" & n

 

[Peltz]

In your first code you have Given each checkbox the same name, because of that when you loop through them (second code) the loop thinks they are all in cell "A1".

I have added a variable "n" to the first code as below and it now seems to work.

  .Display3DShading = True
    n = n + 1
    .Name = "cb" & n

Works like a charm!

Thank you so much.

 

[MickG]

You're welcome