Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Unhappy

    Is there any setting to locked all VBA projects for viewing through one single password OR to lock them by default but unlock individually? I dont have a personal macro book. All macros are within the wb and travel along with.
    Regards
    Prasad

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    There is no setting to lock the VBA project by default.

    You could start a new workbook, lock its VBA project with a password, then save it as an Excel template (.xlt).
    You can then use this template for your new workbooks. The VBA project in those workbooks will be locked with the password you set in the template.

  3. #3
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785190' date='18-Jul-2009 13:48']There is no setting to lock the VBA project by default.

    You could start a new workbook, lock its VBA project with a password, then save it as an Excel template (.xlt).
    You can then use this template for your new workbooks. The VBA project in those workbooks will be locked with the password you set in the template.[/quote]
    I am trying but not that easy. Actually, I have to record macro in WB already exists. furhter, when i open a template, there is an additional "1" added in its name.
    Regards
    Prasad

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    By default, new workbooks are named Book1, Book2, Book3 etc.
    If you create new workbooks from a template MyTemplate.xlt, they will be named MyTemplate1, MyTemplate2, etc.
    If you have named the template Book1.xlt, new workbooks created from it will be named Book11, Book12, etc.
    But that doesn't really matter since you have to give the new workbook a name anyway when you save it.

    The object model does not provide a way to lock or unlock the VBA project using code (virus writers would love that), but you can use SendKeys to lock a project. For example, to lock the project in the active workbook for viewing with password "secret":

    Code:
    Sub LockMe()
      SendKeys "%{F11}%Te^{TAB}%v%Psecret%Csecret~%{F4}"
    End Sub
    This simulates the following keystrokes:
    Alt+F11 - activates the Visual Basic Editor
    Alt+T - activates the "Tools" menu
    e - activates the "VBAProject properties..." menu item
    Ctrl+Tab - activates the second tab (Protection)
    Alt+v - toggles the "Lock project for viewing" check box
    Alt+P - activates the "Password" box
    secret - enters the password
    Alt+C - activates the "Confirm password" box
    secret - enters the password
    ~ - presses Enter to close the dialog
    Alt+F4 - closes the Visual Basic Editor

    But using SendKeys is always tricky. It's language dependent, for example, the above code works in the English version only.

  5. #5
    3 Star Lounger
    Join Date
    May 2008
    Location
    India
    Posts
    306
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='785391' date='20-Jul-2009 12:57']By default, new workbooks are named Book1, Book2, Book3 etc.
    If you create new workbooks from a template MyTemplate.xlt, they will be named MyTemplate1, MyTemplate2, etc.
    If you have named the template Book1.xlt, new workbooks created from it will be named Book11, Book12, etc.
    But that doesn't really matter since you have to give the new workbook a name anyway when you save it.

    The object model does not provide a way to lock or unlock the VBA project using code (virus writers would love that), but you can use SendKeys to lock a project. For example, to lock the project in the active workbook for viewing with password "secret":

    Code:
    Sub LockMe()
      SendKeys "%{F11}%Te^{TAB}%v%Psecret%Csecret~%{F4}"
    End Sub
    This simulates the following keystrokes:
    Alt+F11 - activates the Visual Basic Editor
    Alt+T - activates the "Tools" menu
    e - activates the "VBAProject properties..." menu item
    Ctrl+Tab - activates the second tab (Protection)
    Alt+v - toggles the "Lock project for viewing" check box
    Alt+P - activates the "Password" box
    secret - enters the password
    Alt+C - activates the "Confirm password" box
    secret - enters the password
    ~ - presses Enter to close the dialog
    Alt+F4 - closes the Visual Basic Editor

    But using SendKeys is always tricky. It's language dependent, for example, the above code works in the English version only.[/quote]
    I will definately try this but I think doing lock/unlock manually would be much easier for me.
    Regards
    Prasad

Posting Permissions

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