How to call a workbook-level macro from any sheet

[EdNerd]

This seems like it has an easy answer, but it's eluding me. One of the supervisors created a calendar-styled worksheet, with a column of names and the dates as the top row. There's a section of color codes for different items (probably trainings), and various cells for each person filled with the needed color. I created a macro (in Module 1, not ThisWorkbook) for counting the colors for each name.

The problem I saw was that if I put a Command Button on the sheet, or used a cell and Worksheet_SelectionChange, and he simply created a new blank sheet next month and copied over his colors and names, he would have no way to call the macro. He might put a command in the QAT - but it won't work if he's on vacation and someone else keeps this up for a while. Right now he uses ALT+F8 and selects the macro.

Is there a better way for a situation like this?

 

[Anthony47]

If the "calendar-styled worksheet" contains a button, the button will still be available on a copy of the worksheet with its associated link to the macro. So I don't see the problem, provided that the code of the macro refers to the "active sheet" and not to Sheets("Calendar-Styled-Sheet")

As a more complex solution, you could add a command on the QAT and take this customization with the workbook, so whoever will open the workbook will have the command available. For this, when you customize the QAT you need to specify that the customization has to be saved for the current document; search for "Customize the QAT", or look at the linked image, drop-down-list marked C (sorry, it is not in English, but should be sufficient to locate the position of the option).

 

[EdNerd]

If the "calendar-styled worksheet" contains a button, the button will still be available on a copy of the worksheet with its associated link to the macro. So I don't see the problem, provided that the code of the macro refers to the "active sheet" and not to Sheets("Calendar-Styled-Sheet")

As a more complex solution, you could add a command on the QAT and take this customization with the workbook, so whoever will open the workbook will have the command available. For this, when you customize the QAT you need to specify that the customization has to be saved for the current document; search for "Customize the QAT", or look at the linked image, drop-down-list marked C (sorry, it is not in English, but should be sufficient to locate the position of the option).

Thank you for the reply. The problem with placing any kind of button on the worksheet is that I can not guarantee he will copy the entire sheet - he may just create a new sheet, and then copy over his colors and names. In that case, the button isn't available on the new sheet.

I might be able to feed him instructions on customizing the QAT just for that file. Thank you for that idea.

Ed