How to run a macro if checkboxes are checked in a range

Hi All,

I am trying to configure a button on an excel sheet to send the populated sheet to 2 different recipients. The recipient the email goes to will be dependant on whether a checkbox/ multiple checkboxes in a range are ticked.

At present the sheet I have set up has 2 buttons and requires the user to determine which to press for the email with attached sheet to be sent.

However my boss wants this consolidated into one button which covers all actions. I am sure this is possible, but at the moment it seems to be beyond my abilities.

Simply put:

If checkboxes 1:10 , 22:26 are checked

attach file and send email to recipient

else

if checkboxes 11:21 are checked

attach file and send email to recipient




Current code:

Please Login or Register  to view this content.

Hello franksonata,

Welcome to the Forum!

I made a few changes to the macro. It checks which check boxes are set and decides if the email is to sent or not. The assumption is these are Forms check boxes and not ActiveX.

Please Login or Register  to view this content.

Hello Frank,

The ActiveX check boxes are accessed in a different manner than the Forms type. Here is the code for the ActiveX check boxes.

I noticed in your original post that check boxes 1 through 26 all have to be checked. This version of the macro checks that all 26 are checked. I can change the macro to do different actions based on which check boxes are set if needed. Let me know if you do.

Please Login or Register  to view this content.

Thanks again Leith for your quick response. My code should have read if any check boxes are checked in a particular range.

If any check boxes are checked in range 11-21 then the email needs to be generated and sent to a different address, I have not included this in the code, as needed to sort out check box issue first, I think that is the last caveat!

code as is with your amendments, including additional required 'code'

Please Login or Register  to view this content.

Hello Frank,

This revision of the macro should be fine. Just change the second email address from "a@differentemailaddress.co.uk" to the real one before you run it. It is marked in bold below.

Please Login or Register  to view this content.