Check Checkboxes based on multiply criteria

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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....

1632164812988.png
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I have tried to clarify my needs a little better. It is a duplicate of the above, hopefully just worded a little better. Hope you can understand what I am trying to do.

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 go 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. that are in multiple columns of the sheet. I also have a master checkbox for Discount in the header section of the worksheet (cell Z11). If this box is checked, and 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 like to write some VBA code that would run when the master discount checkbox is checked 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. 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 of that row is greater than $0, if so, mark the discount checkbox in column "P" for that row. If not, leave that rows Discount checkbox blank.

All job line items start in row 8 and go through row 650 (rows 1 -7 has header information about the job). All the checkboxes are Form Control checkboxes that are in column "P" and are currently sequentially numbered starting with Checkbox474 and ending with Checkbox824 (If I add additional rows in the future, then the checkbox number will not be sequential. One option would be to name all the checkboxes so the number didn't matter) 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....

1632164812988.png
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,350
Office Version
  1. 2010
Platform
  1. Windows
what cells are the column P discount check boxes linked to ?
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,350
Office Version
  1. 2010
Platform
  1. Windows
Ignore the previous question.

Try
Pasting this macro into a standard module.
Right click your Master Discount check box and assign it this macro.
VBA Code:
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
                .Enabled = True
                If Cells(.TopLeftCell.Row, "G").Value > 0 Then
                End If
            End If
        End With
    End If
Next chk
        
End Sub
 

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The checkboxes in column P are linked to the their cell in column P. So if the checkbox is in P11 it is linked to P11.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,350
Office Version
  1. 2010
Platform
  1. Windows
remove the .Enabled = True line, it's a left over from some testing and isn't required.
 

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Code doesn't work. I assigned the macro to the "Master discount Checkbox". When I click the mater discount Checkbox to active, true shows up in Z11 link it should, however the individual line discount checkbox does nothing on the lines with the value in column G is greater than $0. When I un-check the master discount checkbox, Z11 turns to False like it should, however it takes about a minute to two minutes to run and it turns off any line discount checkboxes. I already have a macro to clear all the individual line discount checkboxes.
 

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
I removed the .Enabled = True and it still does not work. I am trying to step through it now to see if I can figure it out. My guess it has something to do with the amount being greater than $0. I do appreciate all the help.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,350
Office Version
  1. 2010
Platform
  1. Windows
My guess is the position of the check boxes and the topleftcell not being the cell the check box looks to be in.

What is the code you have for the macro to clear all the individual line discount check boxes ?
 

SBNUT

New Member
Joined
Aug 25, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Private Sub CommandButton1_Click()

Dim ws As Excel.Worksheet

With ActiveSheet
For Each cb In .CheckBoxes
If cb.Left < .Columns(17).Left Then cb.Value = False
Next
End With
End Sub
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,042
Messages
5,767,793
Members
425,435
Latest member
cmardaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top