Macro causes password protection in workbook

[ACG]

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

[NateO]

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

[ACG]

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.

[NateO]

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

[ACG]

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

[NateO]

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

[ACG]

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