Restricting Macro to Selected Sheet

[zimbo109]

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.

ActiveSheet.Unprotect "password"
    
    
    Sheets("Room Template").Visible = True
    Sheets("Room Template").Select
    Rows("1:18").Select
    Selection.Copy
    
    Sheets(" Estimate").Select
    Application.EnableEvents = False
    Selection.Insert Shift:=x1Down
    Application.EnableEvents = True
    ActiveSheet.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowInsertingRows:=True, AllowDeletingRows:=True
    Sheets("Room Template").Visible = xlSheetVeryHidden
    
    
        
End Sub

[Domski]

You could pop this at the start of your code:

If ActiveSheet.Name <> "Estimate" Then

    MsgBox "This can only be run on the Estimate sheet!", vbCritical
    Exit Sub
    
End If

Dom

[zimbo109]

Thanks Dom,

Turning this on its head a bit, is there a way that the same macro could run regardless of what sheet is active. Obviostly the line "Sheets estimate.select" would have to change.

So in effect the macro/button could insert the template in any sheet?

Many thanks
Chris

[JBeaucaire]

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.

Option Explicit

Sub test()

Dim ws As Worksheet
Set ws = ActiveSheet

ws.Protect "password", DrawingObjects:=True, Contents:=True, Scenarios:=True, _
    AllowInsertingRows:=True, AllowDeletingRows:=True, UserInterfaceOnly:=True

    With Sheets("Room Template")
        .Visible = True
        .Rows("1:18").Copy
            Application.EnableEvents = False
                Selection.Insert xlShiftDown
            Application.EnableEvents = True
        .Visible = xlSheetVeryHidden
    End With

End Sub

[zimbo109]

Thanks Jerry - that works, your help has been fantastic!