+ Reply to Thread
Results 1 to 10 of 10

Disable Save As and Save - enable Save via button

  1. 04-06-2011, 02:19 PM #1
    LampCommandr
    LampCommandr is offline
    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.
    Reply With QuoteRegister To Reply
  2. 04-06-2011, 03:24 PM #2
    TMS
    TMS is online now
    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
    44,420

    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
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 04-06-2011, 04:12 PM #3
    LampCommandr
    LampCommandr is offline
    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?
    Reply With QuoteRegister To Reply
  4. 04-06-2011, 04:38 PM #4
    TMS
    TMS is online now
    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
    44,420

    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
    Reply With QuoteRegister To Reply
  5. 04-06-2011, 04:47 PM #5
    LampCommandr
    LampCommandr is offline
    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.
    Reply With QuoteRegister To Reply
  6. 04-06-2011, 04:53 PM #6
    TMS
    TMS is online now
    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
    44,420

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

    workbook, please?
    Reply With QuoteRegister To Reply
  7. 04-06-2011, 05:07 PM #7
    LampCommandr
    LampCommandr is offline
    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.
    Reply With QuoteRegister To Reply
  8. 04-07-2011, 05:31 AM #8
    TMS
    TMS is online now
    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
    44,420

    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
    Reply With QuoteRegister To Reply
  9. 04-07-2011, 09:58 AM #9
    LampCommandr
    LampCommandr is offline
    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!
    Reply With QuoteRegister To Reply
  10. 04-07-2011, 05:42 PM #10
    TMS
    TMS is online now
    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
    44,420

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

    You're welcome. Glad we got there!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1