Can I combine a simple "clear" macro with another that clears checkboxes?

[Maezee]

I have two macros. One simply clears two separate columns on a spreadsheet. The other clears the marked checkboxes in another column. Is there a way to combine them, so that I can click just one button? Here's the code:

Sub CheckBox416_Click()

    Dim ChkBox As Excel.CheckBox
    Dim master As Excel.CheckBox
    
        Set master = ActiveSheet.CheckBoxes(Application.Caller)
        
        For Each ChkBox In ActiveSheet.CheckBoxes
            If ChkBox.Name <> master.Name And master.Value = xlOn Then
                ChkBox.Value = xlOff
            End If
        Next ChkBox
        
End Sub


Sub Clear()
'
' Clear Macro
' Clear Player and Special Request Column
'

'
    Range("I12:I93").Select
    Selection.ClearContents
    Range("G12:G93").Select
    Selection.ClearContents
    Range("G10").Select
End Sub

[JOHN H. DAVIS]

Maybe:

Sub CheckBox416_Click()

    Dim ChkBox As Excel.CheckBox
    Dim master As Excel.CheckBox
    
        Set master = ActiveSheet.CheckBoxes(Application.Caller)
        
        For Each ChkBox In ActiveSheet.CheckBoxes
            If ChkBox.Name <> master.Name And master.Value = xlOn Then
                ChkBox.Value = xlOff
            End If
        Next ChkBox
 Call Clear       
End Sub

[Maezee]

Thank you! I had no idea it would be so simple. Could I also do it in reverse?

[CODESub Clear()
'
' Clear Macro
' Clear Player and Special Request Column
'

'
Range("I12:I93").Select
Selection.ClearContents
Range("G12:G93").Select
Selection.ClearContents
Range("G10").Select

Call CheckBox416_Click()

End Sub][/CODE]

[JOHN H. DAVIS]

Perhaps

Sub Clear()
'
' Clear Macro
' Clear Player and Special Request Column
'

'
Range("I12:I93").Select
Selection.ClearContents
Range("G12:G93").Select
Selection.ClearContents
Range("G10").Select

Call CheckBox416_Click

End Sub

[Maezee]

John, I copied and pasted your code and got an error when I clicked on my "Clear" button.

"Run-time error '1004':
Unable to get the CheckBoxes property of the Worsheet Class."

When I then clicked on "Debug", this line was highlighted: Set master = Active Sheet.CheckBoxes(Application.Caller)

Is this because I assigned the checkboxes macro to another control? Do I need to delete that control first?

I really appreciate your help!

[Solus Rankin]

It looks like you have a space after Active. Make sure its ActiveSheet.CheckBoxes...

Also if both codes 'call' the other then you're going to end up with an endless loop of them going back and forth.

Sub CheckBox416_Click()

    Dim ChkBox As Excel.CheckBox
    Dim master As Excel.CheckBox
    
        Set master = ActiveSheet.CheckBoxes(Application.Caller)
        
        For Each ChkBox In ActiveSheet.CheckBoxes
            If ChkBox.Name <> master.Name And master.Value = xlOn Then
                ChkBox.Value = xlOff
            End If
        Next ChkBox
    Range("I12:I93").ClearContents
    Range("G12:G93").ClearContents

End Sub

[Maezee]

Thank you, Solus. Here is what I have now, and I'm getting the same error message. When I use the "uncheck all" checkbox by itself (without the call), it works; and the "clear" button works alone; but they're not working in combination. I can't work on this again until tomorrow, so there is no rush. I REALLY appreciate your help so far!

Sub Clear()
'
' Clear Macro
' Clear Player and Special Request Column

    Range("I12:I93").ClearContents
    Range("G12:G93").ClearContents
    
Call CheckBox416_Click

End Sub
Sub CheckBox416_Click()

    Dim ChkBox As Excel.CheckBox
    Dim master As Excel.CheckBox
    
        Set master = ActiveSheet.CheckBoxes(Application.Caller)
        
        For Each ChkBox In ActiveSheet.CheckBoxes
            If ChkBox.Name <> master.Name And master.Value = xlOn Then
                ChkBox.Value = xlOff
            End If
        Next ChkBox
        
End Sub

[Solus Rankin]

Can you attach your workbook so we can see specifics?

e/ Different types of checkboxes are handled different ways. From the looks of your code you have activeX controls but I'd like to be able to see the whole picture.

[Maezee]

Being a novice, I couldn't tell whether the check boxes were command or activeX. I know there's a difference, but someone else inserted the checkbox column, and I didn't know how to identify what type it was.

I'm attaching the spreadsheet as it was before I started experimenting with combining the macros. I would prefer to click one button only (preferably the CLEAR button) to accomplish clearing G12:G93, I12:I93 and the check marks in column D.

[Solus Rankin]

try Attachment 259907

[Maezee]

Great solution! Thank you, very much!