+ Reply to Thread
Results 1 to 10 of 10

Disable Save As and Save - enable Save via button

  1. #1
    Registered User
    Join Date
    05-12-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    42

    Disable Save As and Save - enable Save via button

    Hey folks,

    I have the following code in Workbook_BeforeSave, disabling all saving:

    Please Login or Register  to view this content.
    I then have the following code in a module associated to a Save & Exit button, allowing the user to save and rename the file:

    Please Login or Register  to view this content.
    I also have the following code allowing the user to save and overwrite the existing spreadsheet unless it is the template:

    Please Login or Register  to view this content.

    I have a couple of questions:
    1. How do I re-enable saving for these two buttons?
    2. Is it possible to completely grey out the Save As and Save buttons?

    Thanks in advance!
    Last edited by LampCommandr; 04-07-2011 at 02:22 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,300

    Re: Disable Save As and Save - enable Save via button

    Define a global variable, for example:

    Please Login or Register  to view this content.
    Initialise the variable to False. In your Save routines, change the variable to True.

    Then, in youur BeforeSave event, test the variable.

    Please Login or Register  to view this content.

    Regards

  3. #3
    Registered User
    Join Date
    05-12-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Disable Save As and Save - enable Save via button

    Quote Originally Posted by TMShucks View Post
    Define a global variable, for example:

    Please Login or Register  to view this content.
    Initialise the variable to False. In your Save routines, change the variable to True.

    Then, in youur BeforeSave event, test the variable.

    Please Login or Register  to view this content.

    Regards
    Thanks for the quick response. I can't seem to get this to work.

    I declared bAllowSave at the top of the module and set it to False on Private Sub Workbook_Open().

    Am I placing this information in the wrong place?

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,300

    Re: Disable Save As and Save - enable Save via button

    Yes, that's right. And in your Save Routines you'd need to set it to True.

    You maybe need to track the close routines to see what the sequence of events is.

    Basically, if someone just closes the workbook or clicks on the x, then the BeforeCloes event should fire and the save would be cancelled.

    If your save routines are executed, you need to set the variable to true before exiting.

    Post the workbook if you're still having a problem.


    Regards

  5. #5
    Registered User
    Join Date
    05-12-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Disable Save As and Save - enable Save via button

    Quote Originally Posted by TMShucks View Post
    Yes, that's right. And in your Save Routines you'd need to set it to True.

    You maybe need to track the close routines to see what the sequence of events is.

    Basically, if someone just closes the workbook or clicks on the x, then the BeforeCloes event should fire and the save would be cancelled.

    If your save routines are executed, you need to set the variable to true before exiting.

    Post the workbook if you're still having a problem.


    Regards

    What I've done is the following:

    Please Login or Register  to view this content.

    I then have the following on the Save button:

    Please Login or Register  to view this content.
    When I press the button, I get the pop up to rename the file (which is correct since it has the Template name) but the Save As dialog does not pop up. It simply takes me back to the spreadsheet. Pressing OK on the dialog should take me to the Save As dialog.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,300

    Re: Disable Save As and Save - enable Save via button

    workbook, please?

  7. #7
    Registered User
    Join Date
    05-12-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Disable Save As and Save - enable Save via button

    It is attached. I apologize, but I had to trim everything down as there was a lot of proprietary data in the workbook.

    The button and functionality are identical to the other version.

    Thanks for looking into this.

    Noticed that I didn't declare bAllowSave - update is attached.
    Attached Files Attached Files
    Last edited by LampCommandr; 04-06-2011 at 05:15 PM.

  8. #8
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,300

    Re: Disable Save As and Save - enable Save via button

    Probably my fault ;-(

    Try this:

    Please Login or Register  to view this content.

    and

    Please Login or Register  to view this content.


    The Msgboxes are just there to track progress through the routines. You can remove them or comment them out once you are confident that it is doing what you expect.

    Regards

  9. #9
    Registered User
    Join Date
    05-12-2010
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    42

    Re: Disable Save As and Save - enable Save via button

    Perfect! Thanks for spending the time on this!

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    37,300

    Re: Disable Save As and Save - enable Save via button

    You're welcome. Glad we got there!

+ 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