Macro to remove the option to save as if file was open as read only?

[extremecorvette]

Macro to remove the option to save as if file was open as read only?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Running Excel 2003 with Windows 7
<o> </o>
I have a workbook that I have set up with security to recommend open as read-only and if not there is a password that has to be entered so changes can be made to it. But when I open it as read-only and try to close out it give me an option to save-as. Is there a way to remove this option so when everyone is closing out it just closes it with out giving anyone any options to save or save-as when it is opened as read-only?

 

[sous2817]

As a workbook event try:

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

If SaveAsUI Then
MsgBox "The 'Save As' function has been disabled." & Chr(10) & "Only 'Save' will work.", vbInformation, "Save As Disabled"
Cancel = True
End If
End Sub

 

[extremecorvette]

I got it to work but I'm still getting the pop up box asking if I want to save before I close when its open as read only. I also changed it a little bit.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      
If SaveAsUI Then
MsgBox "The Save & Save As function has been disabled." & Chr(10), vbInformation, "Save As Disabled"
Cancel = True
End If
    
End Sub

 

[extremecorvette]

I got it to work but I'm still getting the pop up box asking if I want to save before I close when its open as read only. I also changed it a little bit.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
      
If SaveAsUI Then
MsgBox "The Save & Save As function has been disabled." & Chr(10), vbInformation, "Save As Disabled"
Cancel = True
End If
    
End Sub

 

[sous2817]

How about this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    If SaveAsUI Then SaveAsUI = False
    MsgBox "The Save & Save As function has been disabled." & Chr(10), vbInformation, "Save As Disabled"
End Sub

 

[extremecorvette]

If I open the file and put the macro in it will not let me save and close so I'm not able to see if when I open it up as read only if when I try to close out if it disables the pop up box asking if I want to save.

How about this:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True
    If SaveAsUI Then SaveAsUI = False
    MsgBox "The Save & Save As function has been disabled." & Chr(10), vbInformation, "Save As Disabled"
End Sub

 

[sous2817]

right...I don't think there's a way to design a program where you can make a change so that no saves can be made and expect to be able to save that file so the change you made to diss-allow changes works...kind of a catch-22...

 

[extremecorvette]

This is the first company I have ever worked for that didn't have a internal intranet that I can uses, so I'm stuck using excel and our network.

I was hopping there was a way to have the macro look to see that the file was opened as Read-Only and then not let anyone save or save as and when the workbook closes the macro would stop the pop up for saving before closing from coming up.