Change VBA Code so that it work on All Sheets and not only on Active Sheet.

[Chypp]

I am a noob as far as VBA is concerned. I was helped on this forum with the code below. What I want is to uncheck a Form Check Box automatically on the 28th of the month. The code below works well when I reopen the file but only when the sheet with the CheckBoxes is open at the moment of the opening of the file. If it is not active there's an error with debugging and stuff that I don't get. Is there any way to get it to work regardless of the sheet being active? In all the workbook. I have many sheets with hundreds of Checkboxes. Also I hope that the number assigned to the boxes doesn't repeat with others as it would cause trouble. Thanks.

Please Login or Register  to view this content.

[AliGW]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

[Chypp]

Thanks.
I solved it.

[Stormin']

Do you want to uncheck all checkboxes in the workbook or just certain ones?

[Chypp]

Only certain ones. That's why there was the Number of the CheckBox in the code. But I would like the code to work even when, upon opening of the file, the active sheet is not one of the ones with the Checkboxes. As the code is now, it only works when the sheet's active.

[CO92]

Hi Chypp,

You can introduce the sheet activation procedure into at the beginning of your code, so you can be working in other sheet and run your macro in the good one. You can use something like this:

Please Login or Register  to view this content.

[Stormin']

One last question: is it just one sheet that you would like to clear checkboxes on?
If so, CO92's code above will work, like this:

Please Login or Register  to view this content.

If there are multiple sheets you wish to do then it will be a bit more complicated than the above

[Chypp]

Thanks a lot, Stormin' and CO92! Using your suggestions I have managed to adapt a code where the sheets are activated and the unchecking is done alternatively for the Sheets that contain Checkboxes. They work perfectly. Thanks!

[jolivanes]

For people searching for a solution, like you did, would you mind showing your solution?

[Chypp]

Sure. The solution is very specific to my needs but here it is:
Basically, I needed Excel to automatically uncheck some (not all) of my Form CheckBoxes at the Beginning of the month so that some formulas could trigger Conditional Formatting on a different sheet. The problem was that the code that I had only worked for the active sheet at the moment of opening of the file. With the code below, the good people on this forum helped me consecutively activate the sheets containing the Boxes I needed unchecked before the code did the trick and then return to my overview sheet without causing errors. In case I don't close the file on the 1st of the month, the code below repeats every time I open the file from the 1st to the 3rd of the month. It works well.

Please Login or Register  to view this content.

[jolivanes]

Thank you Chypp.
I am sure that it is very much appreciated by the people looking for a solution with the same or similar problem
A belated Merry Christmas and a Happy and Prosperous New Year to you and yours.

[Stormin']

Thanks for posting your solution. You can also shorten the criteria for calling uncheck (and change it more easily).

Please Login or Register  to view this content.

So uncheck will be called if the day of the month is greater than or equal to 28, or less than or equal to 3 e.g. 28th Dec to 3rd Jan.