VBA Based with Checkboxes

[bnichols]

Hello, looking for some help with some specific coding. I think I listed everything in the mini sheet below, but I'm trying to get a few different codes to run off a box being checked and dependent on multiple cell totals, some by manual entry and some by formula calculation. At the end, I wanted to have a macro button but have it only run once all boxes are checked. I tried a few different ways with codes I found in other threads but haven't had much luck getting it to work. Thanks for any help provided

Fyi im running Excel on a MAC

Test Workbook.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
5
6		Sales	1	2	3	4	5	6		Check Box	Sales Entered?	C:H6	If each cell (not total) is number (nothing), if blank or not number "message" and doesn’t allow box to be checked
7		Labor	1	2	3	4	5	6		Check Box	Labor Entered?	C:H7	Same as above
8		Online Purchases	100	Number result of formula						Check Box	Totals?	C:H9	Same as above
9		Ctuit Totals	1	2	3	4	5	6		Check Box	Online Purchases?	C8	Pop up yes/no message box each checkbox
10		Variance	-200	Number result of formula						Check Box	Variance Less Than $100?	C10	If cell total is between -$100 and $100 (nothing), if less than -100 or greater than 100 "message" and doesn’t allow box to be checked
11
12										Macro Button	Macro runs action that hides rows 6 - 16 and unhides rows 17 - 27, however it will only run when all check boxes are checked
13											If button is pressed, If all boxes not checked run macro, if not all boxes checked "message"
14
2

Cell Formulas
Range	Formula
C8	C8	=50+50
C10	C10	=-100-100

 

[bferraz]

Hey, try code below:

Sub CheckAllBeforeRun()

Dim sh As Worksheet
Set sh = ActiveSheet

Dim check As Boolean

    If sh.Shapes.Count > 0 Then
    
        'Iterate through all the shapes on the sheet
        For Each shp In sh.Shapes
        
            'If shape type is Form Control
            If shp.Type = msoFormControl Then
            
                'If Form Control is Check Box
                If shp.FormControlType = 1 Then
                
                    If shp.ControlFormat.Value <> 1 Then
                    
                    MsgBox ("Not marked.")
                    Exit Sub
                    
                    End If
                 
                End If
                
            End If
                
        Next shp
        
        MsgBox ("All marked.")
        'Call sub
        
    End If

End Sub

It looks for the Form Controls check box as shown below:

 

[bnichols]

Hey, try code below:

Sub CheckAllBeforeRun()

Dim sh As Worksheet
Set sh = ActiveSheet

Dim check As Boolean

    If sh.Shapes.Count > 0 Then
   
        'Iterate through all the shapes on the sheet
        For Each shp In sh.Shapes
       
            'If shape type is Form Control
            If shp.Type = msoFormControl Then
           
                'If Form Control is Check Box
                If shp.FormControlType = 1 Then
               
                    If shp.ControlFormat.Value <> 1 Then
                   
                    MsgBox ("Not marked.")
                    Exit Sub
                   
                    End If
                
                End If
               
            End If
               
        Next shp
       
        MsgBox ("All marked.")
        'Call sub
       
    End If

End Sub

It looks for the Form Controls check box as shown below:

View attachment 81770 View attachment 81771

Thank you, I was able to get that working! Is there a way to get it to use selective checkboxes? The intent was to have 3 sets of the same check boxes on the same sheet and each macro would hide/unhide just a particular set of rows