Checkbox macro from multiple sheets

Wayne T

New Member
Joined
Mar 25, 2014
Messages
20
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).

Code:
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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

gallen

Well-known Member
Joined
Jun 27, 2011
Messages
2,006
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:

Code:
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:
Code:
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.
 
Last edited:

Wayne T

New Member
Joined
Mar 25, 2014
Messages
20
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

New Member
Joined
Mar 25, 2014
Messages
20
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?
 

Forum statistics

Threads
1,143,673
Messages
5,720,222
Members
422,270
Latest member
CaptainMurray

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