CODEWITHSUNDEEP
excelvbacheckbox
user3305177
user3305177

Reputation: 1

Having one checkbox ticking / unticking multiple other checkboxes in Excel VBA

I have an Excel sheet with literally hundreds of checkboxes. I would like some of these checkboxes (that I will refer to as "master checkboxes") to tick or untick a row of 12 other checkboxes. So far I could only find code to do that in an entire worksheet (e.g. untick checkboxes through the entire worksheet), but not in a selected array of checkboxes.

Ideally the macro should understand the boxes to tick / untick in a dynamic fashion (i.e. by looking for them relatively to where the master checkbox is located), as I have many of them to implement, and having to hardcode the address of each checkboxes influenced by each master checkbox would be extremely tedious...

I am not sure whether my boxes are activx or not, but if that can help, I implemented them via a macro using the command: ActiveSheet.CheckBoxes.Add

Thanks for your help!

Upvotes: 0

Views: 4491

Answers (1)

Carl Colijn
Carl Colijn

Reputation: 1617

Those checkboxes are Excel's own; i.e. not the ActiveX ones.

Making code to deduct relationships purely on location of dynamically placed controls is much more involved than naming your controls and just let each control update the right ones. Even when there are hundreds of them ;) The answer here is to automate the control naming process.

The key ingredients here are that an update to such a checkbox must trigger some code to handle the checking/clearing of the rest. Thus you must assign a macro to each master checkbox, which will run when that checkbox is clicked. Or alternatively you could link the checkbox's value to a cell and use the sheet's Update event to run some code (though that's a far less fine-grained solution).

If you want to do it dynamically (i.e. purely on lay-out), then in the macro, check each master checkbox's location and size, and compare it to the location and size of all other checkboxes. Only you can decide what layout criteria result in which child checkbox belongs to which master checkbox (e.g. must it be indented, or is it to the right of it, and also take the location of all other master checkboxes into account - that they're indented underneath one doesn't mean they couldn't belong to another one!)

If you just assign a name to each master and child checkbox, then the macro's become much simpler. In this case I'd opt for using a naming convention: e.g. name each master checkbox "MasterX", and let all child checkboxes use a derived name that starts with the accompanying master checkbox's name, e.g. "MasterX_ChildY". Then you can have generic code that knows which checkbox belongs to which master checkbox. From then on you only need to call a sub like the following from each master checkbox's click macro:

Sub UpdateChildCheckboxes()
  ' Look which master checkbox is clicked
  Dim masterCheckboxName As String
  masterCheckboxName = Application.Caller
  Dim masterCheckbox As CheckBox
  Set masterCheckbox = ActiveSheet.CheckBoxes.Item(masterCheckboxName)

  ' And update all belonging child checkboxes
  Dim nextCheckbox As CheckBox
  For Each nextCheckbox In ActiveSheet.CheckBoxes
    ' Is this a child of this master?
    If InStr(nextCheckbox.Name, masterCheckboxName & "_") = 1 Then
      ' Yes; update it
      nextCheckbox.Value = masterCheckbox.Value
    End If
  Next
End Sub

This sub just sets all child checkboxes to the same state as their masters, so it needs to be enhanced a bit to do specifically what you want (that is something you can only do).

Upvotes: 0

Related Questions