Before SaveAs

[pdauction]

Hi,

I have the following code in Workbook_BeforeSave but it doesn't work when the user uses Save As rather than Save (which they will do every time).

Please Login or Register  to view this content.

I thought you could have Workbook_BeforeSaveAs but that option doesn't seem to be available nor work if I just type it in.

Any ideas please?

Cheers
Paul

[DGagnon]

the coudl should be exicuted before a saveas, you can test this with something like this

Please Login or Register  to view this content.

[pdauction]

Yes the msgbox popped up before the Save As dialog box came up.

As my code uses the current filename it is therefore picking up the old workbook name (pre SaveAs).

Any ideas how this could pick up the name of the saved filename? Presume this might not be possible...

Cheers
Paul

[DGagnon]

i see what your saying, im not sure you can do that, because you would need to save again after the save as. and there is no "AfterSave" event.

you could force a save if yoru sheet name does not match what was in that cell.. something like this

Please Login or Register  to view this content.

but this would now exicute every time there was a change in the book, not just on save.

[Ypma]

Not sure what you do not wish the user to be able to do. If it is that you do not want the save as optiion but keep the save option the following should meet your needs

Private Sub workbook_beforeSave(ByVal SaveAsUI As Boolean, cancell As Boolean)
Dim Ireply As Long
If SaveAsUI = True Then
Ireply = MsgBox("Sorry, you are not allowed to save this " & _
"Workbook as another name. Do you wish to save this " & _
"workbook ?", vbQuestion + vbOKCancel)

Cancel = (Ireply = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If


End Sub

[pdauction]

Many thnaks for the help...

With a slight tweak this is now working

Please Login or Register  to view this content.

If I put the code in before save as well it should do the job.

Thanks again
Paul