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).
Any ideas/help would be greatly appreciated. NB: I am using Form Control Check box, NOT ActiveX.
Many thanks,
Wayne T
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