Macro causes password protection in workbook
Eliminate Pivot Table Annoyances
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Macro causes password protection in workbook

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have a workbook that has a button with the following code - when I execute the code, the workbook is saved with a modification password (as if you were to go into File > Save As > Tools > General Options > Password to Open for file sharing and type in the password of bp02acg). This password is being used in my macro to share the workbook but I don't want the user to have to input a password everytime it is opened. Can anyone help please? Sorry, I'm by no means a VB expert!!!

    Thank you!

    Code:

    Public CurrentFilename As String
    Private Sub CommandButton1_Click()
    [A1].Activate
    Call UnprotectSharing
    Call PrintWorksheet
    Call ProtectSharing
    End Sub

    Sub UnprotectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.UnprotectSharing Password
    End Sub
    Sub ProtectSharing()
    Dim Password As String
    Password = "bp02acg"
    ActiveWorkbook.ProtectSharing Filename:=CurrentFilename, Password:=Password
    End Sub

    Sub PrintWorksheet()
    '
    ' PrintWorksheet Macro
    ' Macro recorded 2/18/2002 by Anita Grimes
    '

    '
    On Error Resume Next
    CurrentFilename = ActiveWorkbook.FullName
    [A3].Activate
    Columns("A:B").EntireColumn.Hidden = True
    Columns("F:G").EntireColumn.Hidden = True
    Columns("R:R").EntireColumn.Hidden = True
    Columns("U:U").EntireColumn.Hidden = True
    ActiveSheet.PageSetup.PrintArea = "$C$1:$AB$51"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Cells.Select
    Range("C1").Activate
    Selection.EntireColumn.Hidden = False
    Columns("V:V").EntireColumn.Hidden = True
    End Sub

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Perhaps if you disable then enable the protection upon opening the file. Try adding the following to your array of macros:

    Private Sub auto_open()
    Application.Run ("UnprotectSharing")
    Application.Run ("ProtectSharing")
    End Sub

    Hope this helps. Cheers,

    Nate

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Unfortunately, I do not want to disable the protection. I may just see if I can share it without a password, I guess that will work provided the users don't go poking around to remove the sharing or anything.

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try the code. Notice it disables the protection, then renables it. It runs your unprotect macro then your protect macro. Worth 10 seconds....

    Cheers,

    Nate

  5. #5
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Nate,

    I added in the code like you recommended, I'm not sure if I added it in the correct area or not though because when I reopen the workbook after closing it I am prompted for a password (which is the same as the sharing password) so I'm assuming the code didn't quite do the trick. I added the code right after Public CurrentFilename As String line (as seen above). Any other ideas?

    Thank you!!!!

    Anita

  6. #6
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I think there's an order of operations issue here. It seems that this is not an Excel password, but a Windows password. This method of sharing is to keep certain users out, if you disable the opening password, the concept seems irrelevant. So, I'm confused as to what the point is....

    Maybe the following will help....Delete that auto_open macro. There may be different way of trying to do what you would like. Via protecting the workbook:

    Protect Workbook
    Allows the creator of a workbook the ability to protect the structure or windows of the workbook with a password. Protection of these two elements of a workbook are:

    Structure of a workbook
    Worksheets and chart sheets in a protected workbook cannot be moved, deleted, hidden, unhidden, or renamed, and new sheets cannot be inserted.

    Windows in a workbook
    Windows in a protected workbook cannot be moved, resized, hidden, unhidden, or closed. Windows in a protected workbook are sized and positioned the same way each time the workbook is opened.

    And/or combing this with locking cells with sheet protection will protect certain contents within the worksheets.

    Regards,

    Nate

  7. #7
    New Member
    Join Date
    Feb 2002
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks Nate, I'll poke around and see what I can find in terms of the different protect options.

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