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

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


--
__________________________________
HTH

Bob

"Zee" wrote in message
...
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

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.


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Zee" wrote in message
...
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

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


On Sep 22, 1:41*pm, Zee wrote:
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.



"Bob Phillips" wrote:
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

--
__________________________________
HTH

Bob

"Zee" wrote in message
...
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- Hide quoted text -

- Show quoted text -

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

On Mon, 22 Sep 2008 10:41:02 -0700, Zee
wrote:


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.


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Zee" wrote in message
...
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

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.

"Gord Dibben" wrote:

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

On Mon, 22 Sep 2008 10:41:02 -0700, Zee
wrote:


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.


"Bob Phillips" wrote:

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


--
__________________________________
HTH

Bob

"Zee" wrote in message
...
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

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

On Sep 22, 5:08*pm, Zee wrote:
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.

"Gord Dibben" wrote:
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

On Mon, 22 Sep 2008 10:41:02 -0700, Zee
wrote:

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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Zee" wrote in message
...
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

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" wrote:

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

On Sep 22, 5:08 pm, Zee wrote:
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.

"Gord Dibben" wrote:
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

On Mon, 22 Sep 2008 10:41:02 -0700, Zee
wrote:

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.

"Bob Phillips" wrote:

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

--
__________________________________
HTH

Bob

"Zee" wrote in message
...
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

This is exactly what Bob's code does.

--JP

On Sep 23, 11:05*am, Zee wrote:
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.

Thanks

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

"JP" wrote:

This is exactly what Bob's code does.

--JP

On Sep 23, 11:05 am, Zee wrote:
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.

Glad to hear it!

--JP

On Sep 23, 12:36*pm, Zee wrote:
Thanks

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

"JP" wrote:
This is exactly what Bob's code does.

--JP

On Sep 23, 11:05 am, Zee wrote:
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.