Forcing a Read Only Filename

[Zee]

Is there anyway to force a read-only file to be saved when closed, the
filename would be a specific cell in the workbook.

Thank you

 

[Bob Phillips]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Me

If .ReadOnly Then

.SaveAs .Worksheets(1).Range("A1").Value
End If
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

 

[Zee]

Hi Bob

I already have the following code written down for a "Before Close" action
and it will not let me add it to it.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
For Each sht In Sheets
sht.Select
If ActiveSheet.ProtectContents = False Then
MsgBox (" ATTENTION STAFF - MAKE SURE TO PROTECT ALL WORKSHEETS PRIOR
TO CLOSING THE FILE")


End If
Next sht
End Sub

THANKS AGAIN FOR YOUR PATIENCE AS I AM STILL VERY NEW TO THIS.

 

[JP]

I think you can combine Bob's code with your own, like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim sht As Excel.Worksheet

For Each sht In Excel.Worksheets
If sht.ProtectContents = False Then
MsgBox ("ATTENTION STAFF - MAKE SURE TO PROTECT ALL WORKSHEETS
PRIOR TO CLOSING THE FILE")
Cancel = True
Exit Sub
End If
Next sht

With Me
If .ReadOnly Then
.SaveAs .Worksheets(1).Range("A1").Value
End If
End With

End Sub


This will cancel the Close until all the sheets are protected, else
pull the filename from Sheet1.A1.

--JP

 

[Gord Dibben]

How about protecting the sheets yourself and not relying on the staff or
bothering them with messages.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.ScreenUpdating = False
Dim N As Single
For N = 1 To Sheets.Count
Sheets(N).Protect Password:="justme"
Next N
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub


Gord Dibben MS Excel MVP

 

[Zee]

Thanks Gord that worked very well.

I am still trying to force it to save when opened as a "read-only" file,
with the filename being a specific cell or named range. Is there also a way
to save it to a specific folder onto the desktop.

Thanks again for your help.

 

[JP]

If you need the user to save the file if it is *opened* as readonly,
you'd need to put Bob's code in the Workbook_Open Event.

With Me

If .ReadOnly Then
.SaveAs .Worksheets(1).Range("A1").Value
End If


If you want to save it to a specific folder, simply pass it as a
string to the SaveAs Method.

..SaveAs "C:\Documents And Settings\Jimmy Pena\Desktop\My SubFolder
\file.xls"

--JP

 

[Zee]

Thanks for the help and this works however the specific cell that is going to
be the filename will not be entered in until the file is opened as a read
only as currently this cell is blank.

 

[JP]

This is exactly what Bob's code does.

--JP

 

[Zee]

Thanks

I put in the "Before Save" application and it works like a charm

 

[JP]

Glad to hear it!

--JP

Thanks

I put in the "Before Save" application and it works like a charm