Macro Save and Auto Password Protect

golfnut324

Board Regular
Joined
Jul 12, 2006
Messages
93
Very new to VBA here. I'm trying to put together a macro that will 1) Password protect all sheets in workbook, 2) Password protect the workbook, and 3) Save workbook with pre-determined name and then append the date and time to the name.

So far I've got the protect sheet (but not password protected) and save workbook with desired appended file name by using this code:



Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Protect ("password")
End Sub
Private Sub Workbook_Open()
Sheets("Sheet1").Protect ("password")
End Sub
Sub Macro6()
'
' Macro6 Macro
'
' Keyboard Shortcut: Ctrl+w
'
'DIM Today As Date
Today = Date
Time = Time
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowSorting:=True, AllowFiltering:=True
ChDir "C:\Users\Craig\Desktop"
ActiveWorkbook.SaveAs Filename:="tblContacts" & Format(Today, "_MMDDYYYY") & Format(Time, "_HHMM")
End Sub


I suspect that I've mixed some apples and oranges here. Can anyone steer me in the right direction. I'm determined to learn this somehow:)

Craig
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Is it?

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Me.Worksheets
    ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
        AllowSorting:=True, AllowFiltering:=True, Password:="password"
Next ws
Me.Protect Password:="password"
Me.SaveAs Filename:="C:\Users\Craig\Desktop\tblContacts\" & Format(Now, "_mmddyyy hh:mm")
End Sub
 
Upvote 0
Sorry to say, after pasting into a module, I'm not sure how to execute the code - steep learning curve here. I am accustomed to executing macros with a short cut key or by running from the "Macro" dialog box. Exactly how do I run this code?

Thanks for your help and patience on this.
 
Upvote 0
Right click the Excel logo
excellogofu8.gif
just to the left of File on the menu bar, select View Code and paste over what's there.
 
Upvote 0
Ok, it's taken me a while, but I finally got to the section of my "Dummies" book that explains beforeclose events and where to install those procedures! I have the code installed and working to this extent: the file closes with all worksheets password protected based on the password placed in the code but the file is saved to the Documents folder and the file name is "FALSE".

Just to review, I'm trying to save to a pre-determined folder on the desktop or in Documents and I want the file to be named "X3C3 GOPD ANALYSIS mmddyyyy hh:mm"

Here's the current code:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Me.Worksheets
ws.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, _
AllowSorting:=True, AllowFiltering:=True, Password:="password"
Next ws
Me.Protect Password:="password"
Me.SaveAs Filename = "C:\Users\Craig\Desktop\X3C3 GOPD ANALYSIS\" & Format(Now, "_mmddyyyy hh:mm")
End Sub



Thank you again for the review and the help.

Craig
 
Upvote 0
Try

Code:
Me.SaveAs Filename:="C:\Users\Craig\Desktop\X3C3 GOPD ANALYSIS\tblContacts" & Format(Now, "_mmddyyyy hh-mm") & ".xls"
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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