+ Reply to Thread
Results 1 to 6 of 6

Before SaveAs

  1. #1
    Registered User
    Join Date
    09-07-2009
    Location
    Lytham, England
    MS-Off Ver
    Excel 2003+07
    Posts
    75

    Before SaveAs

    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

  2. #2
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Before SaveAs

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

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    09-07-2009
    Location
    Lytham, England
    MS-Off Ver
    Excel 2003+07
    Posts
    75

    Re: Before SaveAs

    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

  4. #4
    Forum Expert DGagnon's Avatar
    Join Date
    02-23-2012
    Location
    Ontario, Canada
    MS-Off Ver
    Excel 2003, 2007
    Posts
    1,645

    Re: Before SaveAs

    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.
    Last edited by DGagnon; 04-12-2012 at 10:48 AM.

  5. #5
    Registered User
    Join Date
    02-29-2012
    Location
    Sothampton
    MS-Off Ver
    Excel 2019
    Posts
    16

    Re: Before SaveAs

    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

  6. #6
    Registered User
    Join Date
    09-07-2009
    Location
    Lytham, England
    MS-Off Ver
    Excel 2003+07
    Posts
    75

    Re: Before SaveAs

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1