none
Limit Macro to current workbook

    Question

  • I have two Excel files with two macros in each one. One Macro Protects the all Worksheets and the other one Unprotects them. The difference between files is that one needs a password protection and other one doesn't. If I don't have both files open at the same time, they work fine, but as soon as I'm working with both files at the same time, then macros from one file can be used in the other one. Is there a way to avoid that and to limit macros from other files being execute when they share the same Shortcut keys. Here a sample code for the file with Password protection:

     

    Sub UnprotectSheets()
        '
        ' UnprotectSheets Macro
        ' Macro recorded 7/12/2006

        ' Keyboard Shortcut: Ctrl+Shift+U
        '
        Dim shtCurrent As Worksheet
        Dim strPassword As String
       
        strPassword = InputBoxDK("Type your Password here.", "Password Required")
       
        For Each shtCurrent In ActiveWorkbook.Worksheets
            shtCurrent.Unprotect Password:=strPassword
            Next shtCurrent
            Sheets("ItemsDef").Visible = True
    End Sub

    Sub ProtectSheets()
        '
        ' ProtectSheets Macro
        ' Macro recorded 7/12/2006
        '
        ' Keyboard Shortcut: Ctrl+Shift+P
        '
        Dim shtCurrent As Worksheet
       
        For Each shtCurrent In ActiveWorkbook.Worksheets
            shtCurrent.Protect Password:="**********", _
            DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
            Next shtCurrent
        Sheets("ItemsDef").Visible = xlVeryHidden
    End Sub

    Thanks for the help!

    Friday, July 14, 2006 4:12 AM

Answers

  • Hi again,

    the OnKey method works differently that what I initially thought. Thought it might have been a function that needed to be called for every key stroke but it looks to set up an application wide event handler. What I mean by that is you call the OnKey method once, in the workbook open event, and that key becomes mapped to the function for the duration of the worksheet being open.

    Here is your solution and this works a charmer.

    Put the following in both sheets, remove my last suggestion...

    Private Sub Workbook_Activate()
        Application.OnKey "^+u", "Unprotect"
    End Sub

    Private Sub Workbook_Deactivate()
        Application.OnKey "^+u", ""
    End Sub

    When one workbook becomes active it maps the key to the function while the other workbook becomes deactive and unmaps the key. The net result is only one workbook maps the key at any one time. *takes a bow*

    Wednesday, July 19, 2006 11:32 PM

All replies

  • Hi,

    Make the procedures private, this will prevent any other workbook from being able to call the procedure.

    Private Sub Unprotect

    Friday, July 14, 2006 7:15 AM
  • Hi Derek,

    When I do that, I can't use the shortcut keys anymore and it's not in the list of macros either. How do I keep it private and still being able to use the shortcut keys.

    Thanks,

    Friday, July 14, 2006 4:42 PM
  • really? ok thats good to know, didn't expect that. Your only other option is Friend.

    Friend Sub Test()

    End Sub

    This makes the macro callable from within the workbook but not from outside the workbook. Actually thats more inline with what your looking for. Should work with your shortcut keys but I don't know for sure.

    Saturday, July 15, 2006 12:19 AM
  • Thanks again Derek, but I still can't see them in the list of Macros when I'm in Excel (Tools -> Macro -> Macros) in order to assign the shortcut key. As a matter of fact, I had to create a class module to do the Friend Sub Test(). Any other ideas? Do you know why Excel hides the Private Macros? At least it should let you choose the ones from the current open file. Is there another way to do the shortcut key assignment?

    Thanks,

    Monday, July 17, 2006 4:51 PM
  • Hello Eldorado,

    I tried out all the options and it looks like you can only assign shortcut keys to public macros like you said. I'm finding it difficult to come up with a solution. There is one... try this and see if it does what you need...

    Add a new workbook, in the VBA Editor in the ThisWorkbook object add....


    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Application.OnKey "a", "Hello"
    End Sub

    then create a new module and add the code....

    Public Sub Hello()
        MsgBox "Hello World"
    End Sub

    Everytime you press a you'll get the message. Change the OnKey to handle your shortcut keys and point it to your unprotect method. Since the event is handled by the worksheet the two sheets shouldn't clash it will only be the active workbooks shortcut that will fire.

    Wednesday, July 19, 2006 7:59 AM
  • Derek,

    I still encounter some issues. The code will not work if you try to execute it as soon as you open the file. You'll need to change some thing in the worksheet first and then it'll execute. I'm still getting macros from one file being executed in another file. Let me give you the complete scenario and maybe you can give a better solution.

    I have two Excel files which one is Password Protected and the other one is Protected without any password. Also, we work with both files open at the same time(and I think that's what's creating part of the conflict). I'd like to use the same Shortcut key (Ctrl+Shift+P = Protect and Ctrl+Shift+U = Unprotect) for both files, but only the macro from the file I'm currently working on should run. I'm providing the password in the vba code and with the UserInterfaceOnly:=True because I'm doing sorting in protected cells. Finally, I'm running the protect macros when worbook_open() with UserInterfaceOnly:=True to reset that property. Here is all the code:

    Code for Password requiered file

    Module1

    Private Sub UnprotectSheetsPwd()
        '
        ' UnprotectSheets Macro
        ' Macro recorded 7/12/2006

        '
        ' Keyboard Shortcut: Ctrl+Shift+U
        '
        Dim shtCurrent As Worksheet
        Dim strPassword As String
       
        strPassword = InputBoxDK("Type your Password here.", "Password Required")
       
        For Each shtCurrent In Workbooks("Copy of RFQtesting.xls").Worksheets
            shtCurrent.Unprotect Password:=strPassword
            Next shtCurrent
            Sheets("ItemsDef").Visible = True
    End Sub
    Private Sub ProtectSheetsPwd()
        '
        ' ProtectSheets Macro
        ' Macro recorded 7/12/2006
        '
        ' Keyboard Shortcut: Ctrl+Shift+P
        '
        Dim shtCurrent As Worksheet
       
        For Each shtCurrent In Workbooks("Copy of RFQtesting.xls").Worksheets
            shtCurrent.Protect Password:="********", _
            DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
            Next shtCurrent
        Sheets("ItemsDef").Visible = xlVeryHidden
    End Sub

    ThisWorkbook:

     Dim shtCurrent As Worksheet
       
        For Each shtCurrent In Workbooks("Copy of RFQtesting.xls").Worksheets
            shtCurrent.Protect Password:="******", _
            DrawingObjects:=True, Contents:=True, Scenarios:=True, UserInterfaceOnly:=True
            Next shtCurrent
        Sheets("ItemsDef").Visible = xlVeryHidden
    End Sub


    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Application.OnKey "^+u", "UnprotectSheetsPwd"
        Application.OnKey "^+p", "ProtectSheetsPwd"
    End Sub

    Code for file Protected without Password:

    Module 1

    Private Sub UnprotectSheets()
        '
        ' UnprotectSheets Macro
        ' Macro recorded 7/12/2006
        '
        ' Keyboard Shortcut: Ctrl+Shift+U
        '
        Dim shtCurrent As Worksheet
           
        For Each shtCurrent In Workbooks("Copy of RFQBackOffice.xls").Worksheets
            shtCurrent.Unprotect
            Next shtCurrent
            Sheets("ItemsDef").Visible = True
    End Sub
    Private Sub ProtectSheets()
        '
        ' ProtectSheets Macro
        ' Macro recorded 7/12/2006

        '
        ' Keyboard Shortcut: Ctrl+Shift+P
        '
        Dim shtCurrent As Worksheet
       
        For Each shtCurrent In Workbooks("Copy of RFQBackOffice.xls").Worksheets
            shtCurrent.Protect DrawingObjects:=True, Contents:=True, _
                Scenarios:=True, UserInterfaceOnly:=True
            Next shtCurrent
        Sheets("ItemsDef").Visible = xlVeryHidden
    End Sub

    ThisWorkbook:

    Dim shtCurrent As Worksheet
       
        For Each shtCurrent In Workbooks("Copy of RFQBackOffice.xls").Worksheets
            shtCurrent.Protect DrawingObjects:=True, Contents:=True, _
                Scenarios:=True, UserInterfaceOnly:=True
            Next shtCurrent
        Sheets("ItemsDef").Visible = xlVeryHidden
    End Sub


    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        Application.OnKey "^+u", "UnprotectSheets"
        Application.OnKey "^+p", "ProtectSheets"
    End Sub

    Any help is highly appreciated! I need to submit these files no later than end of the day July 20 (MST)

    Thanks!

    Wednesday, July 19, 2006 10:05 PM
  • Hi again,

    the OnKey method works differently that what I initially thought. Thought it might have been a function that needed to be called for every key stroke but it looks to set up an application wide event handler. What I mean by that is you call the OnKey method once, in the workbook open event, and that key becomes mapped to the function for the duration of the worksheet being open.

    Here is your solution and this works a charmer.

    Put the following in both sheets, remove my last suggestion...

    Private Sub Workbook_Activate()
        Application.OnKey "^+u", "Unprotect"
    End Sub

    Private Sub Workbook_Deactivate()
        Application.OnKey "^+u", ""
    End Sub

    When one workbook becomes active it maps the key to the function while the other workbook becomes deactive and unmaps the key. The net result is only one workbook maps the key at any one time. *takes a bow*

    Wednesday, July 19, 2006 11:32 PM
  • Hi Derek,

    I did some preliminary testing and it works like a charm!!! Thanks so much for coming back so quickly with an answer!!! Now I'll be able to finish up the project the way I wanted.

    Thanks again!

    ElDorado.-

    Thursday, July 20, 2006 4:38 AM