Check Checkboxes based on multiply criteria

[SBNUT]

I have a Job worksheet that has several hundred lines in it for different aspects of a job. When we are bidding a job, my employees goes through the list and selects which lines are needed to complete the job. A job could consist of one line, or several hundred lines. I have several checkboxes in this worksheet to control if a discount is taken, if sales tax is calculated on the chosen line, etc. I have a master checkbox for Discount. If this box is checked, then if the discount checkbox on the individual line is checked, the discount is calculated for that line. This is all working fine. However, the employees have to individually select each discount checkbox in each line of the job. I would only want to select the individual discount checkbox in a line if 1) the master checkbox in cell z11 was true, and 2) if the value in cell G plus the individual line number is greater than $0. SO let me try and say that a little different. If the master checkbox in Z11 is "True", then loop through each row starting with row 8 and check if the value in column g is greater than $0, mark the discount checkbox for that row. All job line items start in row 8 and go through row 650. All the checkboxes are Form Control checkboxes and are currently sequential for Checkbox474 to Checkbox824 (If I add additional rows in the future, then the checkbox number will not be sequential. All the discount checkboxes are in column "P" I could do this very easy with and IF/And statement in the cell the checkbox is linked to. However, if the employee "Un-Checks" the checkbox, it wipes out the formula. So I was thinking that VBA would be the way to go. At each change in the master checkbox, VBA code could check and mark the appropriate lines. I know just enough about VBA to be dangerous, so this is a little about my ability without some help. Thanks in advance for the help....

 

[NoSparks]

My bad
must have deleted wrong line

Sub MasterDiscount_CheckBox()

    Dim chk As CheckBox, bMaster As Boolean
    Dim rng As Range
  
bMaster = Range("Z11")
For Each chk In ActiveSheet.CheckBoxes
    'check for being in column P below row > 8
    If chk.TopLeftCell.Column = 16 And chk.TopLeftCell.Row > 8 Then
        With chk
            'the linked cell
            Set rng = Range(Replace(.LinkedCell, "$", ""))
            If bMaster = False Then
                rng.Value = "False"
            Else
                If Cells(.TopLeftCell.Row, "G").Value > 0 Then
                    rng.Value = "True"
                End If
            End If
        End With
    End If
Next chk
End Sub

 

[SBNUT]

Thanks, That worked. I still need to figure out why when the master checkbox is unchecked it talks so long to run.

 

[NoSparks]

I put 500 check boxes down column P and it took the same amount of time to find the check boxes and write to the linked cell for both checked and unchecked.
Let's try disabling events and calculations at the beginning of the macro and re-enabling them at the end and see if maybe it's formulas or events that cause the difference.

Sub MasterDiscount_CheckBox()

    Dim chk As CheckBox, bMaster As Boolean, rng As Range
  
' Disable events and screen updating
With Application
  .EnableEvents = False
  .Calculation = xlCalculationManual
  .ScreenUpdating = False
End With
' Trap errors
On Error GoTo exit_

bMaster = Range("Z11")
For Each chk In ActiveSheet.CheckBoxes
    'check for being in column P below row > 8
    If chk.TopLeftCell.Column = 16 And chk.TopLeftCell.Row > 8 Then
        With chk
            'the linked cell
            Set rng = Range(Replace(.LinkedCell, "$", ""))
            If bMaster = False Then
                rng.Value = "False"
            Else
                If Cells(.TopLeftCell.Row, "G").Value > 0 Then
                    rng.Value = "True"
                End If
            End If
        End With
    End If
Next chk

exit_:
' Restore events and screen updating
With Application
  .EnableEvents = True
  .Calculation = xlCalculationAutomatic
  .ScreenUpdating = True
  ' Show the reason of error
  If Err Then MsgBox .Err.Description, vbCritical, "Error #" & Err.Number
End With

End Sub

 

[SBNUT]

NoSparks,
That helped a lot. Thanks for all your help.

 

[NoSparks]

You're welcome, thanks for letting me know.

Please mark as answered