Password Macro Help
Password Macro Help
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Password Macro Help

  1. #1
    Guest

    Default

     
    Thanks to some of the replys last week, I now have a macro to create a password that will prevent others printing a worksheet.

    However, this doesn't work if someone deactivates the macro using macro protection. Is there a way around this?

    Many thanks for the help in advance.

    Bazil

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi

    There is no sure way of preventing this as it's designed to protect users from possible viruses. I do however have a method than can be used to encourage users to activate macros, if they choose no they are left looking at a blank Worsheet.

    Go here:
    http://www.ozgrid.com/download/default.htm
    and download: EnableMacros.zip




  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    The only satisfactory I've come across for solving this problem is to do the following.

    In your workbook add a new sheet called something like Macros Disabled and put a message on it saying 'This workbook won't function without Macros Enabled. Please close and reopen ensuring you click the Enable Macros button.'

    Now run this code.
    Code:
    Sub HideSheets()
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> "Macros Disabled" Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next
    End Sub
    Now, a sheet hidden with xlSheetVeryHidden can't be unhidden without VBA code (i.e. you can't choose Format, Sheet, Unhide).

    The next thing to do is to include some code in your Workbook_Open event to unhide your sheets. Obviously, the Workbook_Open event will only execute if macros are enabled. To get to the workbook code module right click the second Excel icon from the top left and choose View Code. Then select Workbook from the left dropdown box - this will create a blank procedure. Something like this will do the trick:-

    Code:
    Sub HideSheets()
    For Each sh In ActiveWorkbook.Sheets
        If sh.Name <> "Macros Disabled" Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next
    End Sub
    You should also include some code in the BeforeClose like this:-

    Code:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Sheets("Macros Disabled").Visible = xlSheetVisible
    
    For Each sh In ThisWorkbook.Sheets
        If sh.Name <> "Macros Disabled" Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next
    ThisWorkbook.Save
    End Sub
    HTH,

    Any problems, let me know.

    D

  4. #4
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,936
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Dave, I started writing that post before I saw yours. Long time, no see

    Regards,
    D

  5. #5
    Guest

    Default

      
    Many thanks Chaps - I'll have a go.

    Bazil

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com