Running 3 macros from 1 button help

[cgfitzp]

Hello Group;

Please help with my problem.

Thanks,
Chet

I have a workbook which contains 2 sheets, I have 3 macro's which clear contents of selected cells in each sheet, another Module1 to reset check boxes in sheet 2. Each macro works when run in macros on the developer menu.

I have a button on sheet1 I want to use to run the 3 macros. Each macro runs when assigned individually to the button. The macro called Main_Macro MoDule2 is setup to run the 3 macros from the sheet1 button. I've sen examples of this process using cal and sub plus the macro name.

When using the example below I get a compile error "expected end sub"
When using cal I get compile error "sub or function not defined"



Sub Main_Macro()

Sub ClearCheckBoxes() 'Macro1
Sub ClearMyRange() 'Macro2
Sub ClearMyRange2() 'Macro3


End Sub


Sub clearcheck()
Dim sh As Worksheet
For Each sh In Sheets
On Error Resume Next
sh.CheckBoxes.Value = False
On Error GoTo 0
Next sh
End Sub

Sub ClearMyRange()
'
' ClearMyRange Macro
' Sub ClearMyRange


Range("C3:AA64 ").ClearContents
Range("A2 ").ClearContents




End Sub






Sub ClearMyRange2()
'
' ClearMyRange Macro
' Sub ClearMyRange


Range("B6:K10 ").ClearContents
Range("E21:E22 ").ClearContents
Range("E24:E25 ").ClearContents
Range("G23:I23 ").ClearContents
Range("B30:G37 ").ClearContents
Range("B53:K57 ").ClearContents
Range("D51 ").ClearContents
Range("F51 ").ClearContents
Range("B70:K77 ").ClearContents
Range("H44:K49 ").ClearContents
Range("C42:F42 ").ClearContents
Range("H30:K37 ").ClearContents


End Sub '

 

[JackDanIce]

Try:

Sub Main()

    ClearCheckBoxes
    ClearMyRange
    ClearMyRange2

End Sub

Sub ClearCheckBoxes()

    Dim x   As Long
    
    For x = 1 To Worksheets.Count
        On Error Resume Next
        sheets(x).CheckBoxes.Value = 0
        On Error GoTo 0
    Next x
        
End Sub

Sub ClearMyRange()

    Range("A2").ClearContents
    Range("C3:AA64").ClearContents

End Sub

Sub ClearMyRange2()


    Range("B6:K10").ClearContents
    Range("E21:E22").ClearContents
    Range("E24:E25").ClearContents
    Range("G23:I23").ClearContents
    Range("B30:G37").ClearContents
    Range("B53:K57").ClearContents
    Range("D51").ClearContents
    Range("F51").ClearContents
    Range("B70:K77").ClearContents
    Range("H44:K49").ClearContents
    Range("C42:F42").ClearContents
    Range("H30:K37").ClearContents
   
End Sub

 

[cgfitzp]

Try:

Sub Main()

    ClearCheckBoxes
    ClearMyRange
    ClearMyRange2

End Sub

Sub ClearCheckBoxes()

    Dim x   As Long
    
    For x = 1 To Worksheets.Count
        On Error Resume Next
        sheets(x).CheckBoxes.Value = 0
        On Error GoTo 0
    Next x
        
End Sub

Sub ClearMyRange()

    Range("A2").ClearContents
    Range("C3:AA64").ClearContents

End Sub

Sub ClearMyRange2()


    Range("B6:K10").ClearContents
    Range("E21:E22").ClearContents
    Range("E24:E25").ClearContents
    Range("G23:I23").ClearContents
    Range("B30:G37").ClearContents
    Range("B53:K57").ClearContents
    Range("D51").ClearContents
    Range("F51").ClearContents
    Range("B70:K77").ClearContents
    Range("H44:K49").ClearContents
    Range("C42:F42").ClearContents
    Range("H30:K37").ClearContents
   
End Sub

Thanks Jack

I get sun function not defined error

 

[mikerickson]

Did you correct the spelling of ClearCheck / ClearCheckBoxes

 

[cgfitzp]

Thanks Mike;

I changed the macro to;

Sub ClearCheckBoxes()
'Updateby Extendoffice 20161129
Dim chkBox As Excel.CheckBox
Application.ScreenUpdating = False
For Each chkBox In Sheet2.CheckBoxes
chkBox.Value = xlOff
Next chkBox
Application.ScreenUpdating = True
End Sub


Now I get an error in the Main Macro

Can/t excite in code in break mode at the ClearMyRange


Sub Main_Macro()
ClearCheckBoxes
ClearMyRange
ClearMyRange2

End Sub

 

[cgfitzp]

Apparently Excel didn't like me trying to run 2 clear content macros. I combined the clear contents macro and both sheets clear and the check boxes reset.
see below

Sub Main()


ClearCheckBoxes
clearcontents


End Sub


Sub clearcontents()


'Clearing fields experiment
On Error Resume Next

With Worksheets("Red Book")
.Unprotect
.Range("C3:E64 ").clearcontents
.Range("A2 ").clearcontents
.Range("C3:E95 ").clearcontents
.Range("G3:H95 ").clearcontents
.Range("J3:L95 ").clearcontents
.Range("N3:R95 ").clearcontents
.Range("T3:AA95 ").clearcontents
.Protect
End With

With Worksheets("Month End")
.Unprotect
.Range("B6:K9 ").clearcontents
.Range("E21:E22 ").clearcontents
.Range("D16:D17 ").clearcontents
.Range("E24:E25 ").clearcontents
.Range("G23:I23 ").clearcontents
.Range("B30:G37 ").clearcontents
.Range("C42:F42 ").clearcontents
.Range("B53:K57 ").clearcontents
.Range("H44:K49 ").clearcontents
.Range("B64:E64 ").clearcontents
.Range("D51 ").clearcontents
.Range("D19 ").clearcontents
.Range("F51 ").clearcontents
.Range("B70:K77 ").clearcontents
.Protect
End With

End Sub


ub ClearCheckBoxes()
'Updateby Extendoffice 20161129
Dim chkBox As Excel.CheckBox
Application.ScreenUpdating = False
For Each chkBox In Sheet2.CheckBoxes
chkBox.Value = xlOff
Next chkBox
Application.ScreenUpdating = True




End Sub


Chet