Forcing a Save As

[daveszt]

Hi All,

I've been given the task of trying to force Excel to always go to the Save As menu rather than only when being initially saved.

Does anyone know the code that i could use that might force excel to always bring up the Save As menu througout?

Thanks,

DaveSzt

[dominicb]

Good morning davezt

It's quite an interesting question, because although it is normally possible to disable a menu, disabling the icon and the hotkey shortcuts normally causes problems. After a fair bit of thinking I had an inspirational thought - you need to intervene just before a save is done and this can be done using the Before_Save event procedure.

Open the file you want this routine to affect and press ctrl + f11. If you're inexperienced with VBA then make this the only file open at present. In the project window (usually top left) find your file and double click on ThisWorkbook and copy the code below into the blank space provided.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)
MsgBox "You cannot directly save this application." _
& Chr(13) & "Please select another filename"
Application.Dialogs(xlDialogSaveAs).Show
End Sub

When you next attempt to save the workbook your message will appear and the save as dialog box will open. Of course there's nothing to stop the user from saving the file back over itself...

HTH

DominicB

[daveszt]

thanks very much. i'll give it a bash.

Dave