Results 1 to 5 of 5

Restricting Macro to Selected Sheet

Threaded View

zimbo109 Restricting Macro to Selected... 03-23-2010, 05:10 AM
Domski Re: Restricting Macro to... 03-23-2010, 06:11 AM
zimbo109 Re: Restricting Macro to... 03-23-2010, 04:14 PM
JBeaucaire Re: Restricting Macro to... 03-23-2010, 04:48 PM
zimbo109 Re: Restricting Macro to... 03-23-2010, 05:18 PM
  1. #1
    Forum Contributor
    Join Date
    02-05-2010
    Location
    Exeter, England
    MS-Off Ver
    Excel 2007 & 2010
    Posts
    180

    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.


    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
    Last edited by zimbo109; 03-23-2010 at 05:17 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1