Read only Spreadsheet, Save As non-Read Only

[moogthemoog]

I have a Spreadsheet that has Read Only Recommended. When a user wants to Save it, I want a new Filename entered by the user, and to make sure that the Read-Only Recommended status is switched off (i.e. Read-Write always On)

Although my users can do this by File-SaveAs, most of them forget to switch the Read only recommended status off in the SaveAs dialog box.

What would be the code for this? My code below falls over on the Workbook.SaveAs line. I'm trying to use xlDialogSaveAs as the filename, but it's failing also!

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

Application.EnableEvents = False
Application.Dialogs(xlDialogSaveAs).Show
If Not Me.ReadOnly Then
Workbook.SaveAs Filename:=xlDialogSaveAs, ReadOnlyRecommended:=False
End If
SaveAsUI = False
Cancel = True
Application.EnableEvents = True

End Sub

 

[ranman256]

saving with SAVE AS should release the 'Read Only' by default.

 

[moogthemoog]

saving with SAVE AS should release the 'Read Only' by default.

Unfortunately not; at least, not in Excel 2007. The tick box for "Read-only recommended" remains ticked.

 

[ranman256]

ActiveWorkbook.SaveAs Filename, , , , False

 

[moogthemoog]

Unfortunately, this still doesn't work.
I've just tried something, and using SaveAs does clear the Read Only by default - but only when the Property of the file is set as Read Only within Windows Explorer.

However, if the file is set to "Read-only recommended" via the checkbox on the Excel SaveAs Dialog box (using Tools/General Options), then the code doesn't work, and it would appear that when manually using SaveAs, the user must remember to untick that checkbox in the General Options.

I was hoping for VBA to force the issue...