Restricting Macro to Selected Sheet

Hi, The following macro inserts a template from "room template" into "estimate" which is assigned to toolbar button. The problem I have is that if a user presses the button regardless of what sheet they have active the macro runs and unlocks the "active sheet" and then errors as it can't perform the rest of the function and everything is unlocked! What I would like to achieve is that : If the user is not on the "estimating sheet" then a message box appears stating "not allowed" with just a cancel button and the macro does not run.

Please Login or Register  to view this content.

You could pop this at the start of your code:

Please Login or Register  to view this content.

Dom

A couple of things this macro can show.

1) You don't have to unprotect a sheet for VBA to act on it. You simply need to reapply the protection with a non-persistent flag UserInterfaceOnly:=True turned on. This keeps the protection active for the user, but not to VBA.

2) You don't need to "select" things to act on them via VBA. This macro shows how to address those ranges directly using a complete object address, so your macro never leaves the activesheet.

Please Login or Register  to view this content.