Macro causes password protection in workbook

ACG

New Member
Joined
Feb 17, 2002
Messages
10
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Thanks Nate, I'll poke around and see what I can find in terms of the different protect options.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top