Checkbox macro from multiple sheets

[Wayne T]

Hi all,

Apologies if this has been answered, but I've just spent an hour scanning the threads and haven't found a solution that fits my needs, hoping someone can help.

I have set up a workbook as a template that I use to email monthly statements to individual customers. Each has their own tab, which is populated from a pivot table on a master sheet. I have a macro (button on screen) that deletes the tabs of customers with no transactions for the month, then emails the remaining statements directly from excel. This works a treat, and I'm quite proud of it

On the master sheet I also have a checkbox that needs to be checked BEFORE the 'Send Email' macro button appears on screen, basically it asks "Have you checked each statement for errors?". This also works just fine, however I have now been asked if it's possible to put a checkbox on each tab, and have the 'Send Email' button hidden unless all THOSE check boxes are checked. I frankly have no idea how to go about this....

This is the code I'm currently using to hide/unhide the 'Send Email' button, but I am stumped as to how I might modify it so it works from checkboxes from multiple sheets, (which wont always be the same).

Sub CheckBox1_Click()

If ActiveSheet.CheckBoxes("Check Box 3").Value = 1 Then
    ActiveSheet.Buttons("Button 2").Visible = True
Else
    ActiveSheet.Buttons("Button 2").Visible = False
End If

End Sub

Any ideas/help would be greatly appreciated. NB: I am using Form Control Check box, NOT ActiveX.

Many thanks,
Wayne T

 

[gallen]

Is the checkbox named the same on each sheet?

If so, you need a public function that checks the checkboxes on each sheet. If any aren't checked then don't show the button so something like:

Paste this into a module:

Function ShowButton() As Boolean
'This code needs to be in a module
    Dim ws As Worksheet
    For Each ws In Worksheets
        If ws.CheckBoxes("Check Box 3").Value = 0 Then
            ShowButton = False
            Exit Function
        End If
    Next ws
    'If we get here all checkboxes are true
    ShowButton = True
End Function

Then instead of your code you posted something like:

Sub CheckBox1_Click()
    ActiveSheet.Buttons("Button 2").Visible = ShowButton
End Sub

Please note though that using 'Activesheet' can cause headaches. Much better to fully qualify the sheet you are wanting to run code on.

 

[Wayne T]

Hi gallen,

Thanks so much for the speedy reply. It was late Friday afternoon here when I posted, thinking the weekend would give time to get a reply by Monday, and here you are replying within an hour!!
I have no access from home, so can't check anything till Monday morning, but that looks exactly like what I'm after. Will let you know first thing Monday, and again, many thanks for such a swift and helpful response.

Cheers,
WT

PS: Yeah, I know 'ActiveSheet' is a little iffy, was just being lazy, and honestly...wasn't sure it was going to work lol. Will amend to correct syntax when it's all working, fingers crossed.

 

[Wayne T]

Hi again gallen,

Sorry for the delay in reply, EOM craziness at work. Sadly, the code you posted doesn't appear to work, for me at least. All check boxes are named the same, but what's happening is that regardless of whether they're checked or not, the 'Send email' button appears when 'ActiveSheet' check box is ticked, (this is the master sheet, and have renamed it in code appropriately) then doesn't disappear if that check box, or any others are unchecked. Any ideas on what I may be doing wrong?