return to previously selected sheet and password protect

[josh_m]

i have this code in my workbook that runs each time a user saves the workbook.
problem is that i want to password protect the sheet and then return to the sheet the user was previously working in. so looking for 2 modifications to my code below:
1. to unprotect the sheet, then protect it when done
2. return the user to whatever sheet they were working in when they saved the workbook
thanks in advance for any help you can give

Option Explicit

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False
Sheets("Vacations").Select

'would like code here to unprotect sheet ("Vacations"), pw = "password"

Range("a2").Value = FormatDateTime(Now, vbShortDate)
Range("B2").Value = FormatDateTime(Now, vbShortTime)

'would like code here to protect sheet ("Vacations"), pw = "password"
'would like code here to go back to sheet user had selected before saving

Application.ScreenUpdating = True

End Sub

 

[VoG]

No need to select the sheet. Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False
With Sheets("Vacations")
    
    'would like code here to unprotect sheet ("Vacations"), pw = "password"
    .Unprotect Password:="password"
    .Range("a2").Value = FormatDateTime(Now, vbShortDate)
    .Range("B2").Value = FormatDateTime(Now, vbShortTime)
    
    'would like code here to protect sheet ("Vacations"), pw = "password"
    'would like code here to go back to sheet user had selected before saving
    .Protect Password:="password"
End With
    
Application.ScreenUpdating = True

End Sub

 

[josh_m]

fantastic! thank you, works great

 

[josh_m]

VoG - maybe you can help me again.
I wanted to expand on the actions to be performed "on save"

i wanted to add the current date and time to the center footer for all the other sheets in the workbook, so I tried this, but it was not working. (please forgive me if i butchered VBA with what I did. i just tried recording a macro and picking out the code needed)



Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Application.ScreenUpdating = False
With Sheets("Vacations")


.Unprotect Password:="password"
.Range("a2").Value = FormatDateTime(Now, vbShortDate)
.Range("B2").Value = FormatDateTime(Now, vbShortTime)

.Protect Password:="password"
End With
'
'
'
'
With Sheets("Feb 12", "Mar 12", "Apr 12", "May 12", "Jun 12", "Jul 12", "Aug 12", "Sep 12", "Oct 12", "Nov 12", "Dec 12", "Jan 13")
With ActiveSheet.PageSetup
.CenterFooter = "last saved &D &T"

End With

Application.ScreenUpdating = True

End Sub

 

[VoG]

Untested

With Sheets(Array("Feb 12", "Mar 12", "Apr 12", "May 12", "Jun 12", "Jul 12", "Aug 12", "Sep 12", "Oct 12", "Nov 12", "Dec 12", "Jan 13"))
 .PageSetup.CenterFooter = "last saved &D &T"
End With

 

[josh_m]

again, tyvm!

 

[josh_m]

so i have gone and mucked around with the code and everything works just the way i want it to, with one caveat. the footer is now always the current date/time. it does not stay static to the date/time at the last save. thoughts?

Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Application.ScreenUpdating = False
'
'
Dim wksh As Worksheet
For Each wksh In ThisWorkbook.Worksheets
wksh.Unprotect Password:="T"
Next wksh
Set wksh = Nothing
'
'
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws.PageSetup
.CenterFooter = "&22last saved &D &T"
End With
Next ws
Set ws = Nothing
'
'
Dim ws3 As Worksheet
For Each ws3 In ThisWorkbook.Worksheets
ws3.Range("a2").Value = FormatDateTime(Now, vbShortDate)
ws3.Range("B2").Value = FormatDateTime(Now, vbShortTime)
Next ws3
Set ws3 = Nothing

'
'
Dim ws2 As Worksheet
For Each ws2 In ThisWorkbook.Worksheets
ws2.Protect Password:="T"
Next ws2
Set ws2 = Nothing
'
'
Application.ScreenUpdating = True

End Sub

 

[VoG]

Try

.PageSetup.CenterFooter = "last saved " & Format(Now, "mm/dd/yyyy hh:mm")