Relative Cell Reference for a "Select All" Checkbox Macro

[Ninja97]

I have a spreadsheet with many checkboxes and a checkbox determines if a row is going to be used in some calculations. Some sections have many rows, and to make it easier to select many row at once, I though it would be a great idea to add a "select all" checkbox. My spreadsheet was "macro free" up until this point. To achieve this, I found myself diving into VBA and macros.

I found this section of code somebody else presented as a solution to somebody else asking how to make a "select all" checkbox, and it worked. I added "SS" in the name field of all the checkboxes I wanted included and used this code below:

Sub SSCheckBoxes()
Application.ScreenUpdating = False
'Using Form Checkbox so it is a Shape object
Dim cbShape As Shape
'If the word "SS" is found in the checkbox name then check the box.
For Each cbShape In ActiveSheet.Shapes
If InStr(1, cbShape.Name, "SS", vbTextCompare) > 0 Then cbShape.ControlFormat.Value = xlOn 'xlOff
Next cbShape
Application.ScreenUpdating = True
End Sub

The problem is, I want the "select all" to be conditional. The checkbox in each row has a cell 3 columns to the left of it referencing quantity. I only want the checkbox to get selected when the checkbox name has "SS" in it, AND the cell 3 columns to the left has a value greater than 0. I thought I could do this, but I have learned it is over my head.

Can anybody give me the correct addition to the code above to achieve what I am trying to do?

 

[L. Howard]

A bit of a long shot here, but do the checkboxes have a linked cell?

If the cell in question of value > 0 IS the linked cell, try the first line as your If statement...

If the linkedcell is the same cell the checkbox is in, then try the second If statement.

 If InStr(1, cbShape.Name, "SS", vbTextCompare) > 0 And cbShape.LinkedCell.Value > 0 Then cbShape.ControlFormat.Value = xlOn 'xlOff


 If InStr(1, cbShape.Name, "SS", vbTextCompare) > 0 And cbShape.LinkedCell.Offset(, -3).Value > 0 Then cbShape.ControlFormat.Value = xlOn 'xlOff

Howard

 

[Ninja97]

Howard,

The linked cell is the same cell that the checkbox is in, so your 2nd example is what would apply. However, I already tried that, with the only exception being I had (0, -3) where you have (, -3). It resulted in, "Run-time error '438: Object doesn't support this property or method"

Last night I decided to abandon this way of doing it, and have just hard-coded the macro to go through the specific cells I'm interested in and then turn each checkbox on or off. See below:

Sub StatusCheckBoxes()
Application.ScreenUpdating = False
'Using Form Checkbox so it is a Shape object
Dim i As Integer
If Cells(36, 12) = True Then
    For i = 6 To 35
        If Cells(i, 11).Value > 0 Then Cells(i, 12).Value = True
    Next i
Else
    For i = 6 To 35
        Cells(i, 12).Value = False
    Next i
End If
Application.ScreenUpdating = True
End Sub

Thank you for responding with an answer as quickly as you did, though. I appreciate it!