+ Reply to Thread
Results 1 to 5 of 5

Problems with BeforeClose Event code

  1. #1
    Registered User
    Join Date
    10-12-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    2

    Problems with BeforeClose Event code

    Greetings,

    I have a worksheet that will be edited by many users. In order to force the users to enable macros before working in the spreadsheet, I have created a worksheet called "START" which will be visible to the user only when macros are disabled because a macro must be run to hide it. This is written in the BeforeClose event procedure in ThisWorkbook. I also want to disable the "save" prompt because it is triggering a hangup (not related to my current problem). I have attempted to do this by setting ThisWorkbook.Saved = True. I am fine with the program closing without the user having a chance to save their work, but I want to display a message box telling them this is the case. The problem is, the code doesn't execute all 3 parts when the workbook is closed. As it is written below, the code does not unhide the "START" sheet.

    Private Sub Workbook_BeforeClose(Cancel As Boolean)

    MsgBox "If you have not saved your work, it is too late! You will need to reopen the Save Log and start over. Click 'Save' before closing."

    ' Unhide Start page for Macro reminder
    Dim ws As Worksheet

    ' Unhide the START worksheet
    Sheets("START").Visible = xlSheetVisible

    'Loop through all worksheets
    For Each ws In ThisWorkbook.Worksheets

    'Check each worksheet name
    If ws.Name <> "START" Then

    'Hide the sheet
    ws.Visible = xlVeryHidden
    End If

    'Loop to next worksheet
    Next ws

    ActiveWorkbook.Saved = True

    End Sub

    If I remove the MsgBox, it works fine, but with no warning to the user that they have just closed without saving. If I remove 'ActiveWorkbook.Saved = True', then I'll get the message box, but then the save prompt box returns. Is there any way to have it written so that when the user clicks 'close,' the "START" page appears along with my MsgBox, and no prompt from Excel to save? I'm very new to Excel and VBA, any help would be greatly appreciated!

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,482

    Re: Problems with BeforeClose Event code

    The very 1st line of code could be.
    Please Login or Register  to view this content.
    Then you don't require the msgbox

  3. #3
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Problems with BeforeClose Event code

    Hi tmbarnard

    Welcome to the Forum!

    Please use Code Tags around your Code...it's a Forum Requirement. To do so, in your Original Post, highlight your code and click the # icon at the top of your post window.

    See if this does as you require
    Please Login or Register  to view this content.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  4. #4
    Registered User
    Join Date
    10-12-2014
    Location
    Denver, CO
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Problems with BeforeClose Event code

    Thanks jaslake, this works perfect!
    Last edited by tmbarnard; 01-03-2015 at 05:50 PM.

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Problems with BeforeClose Event code

    You're welcome...glad I could help.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Msgbox in BeforeClose event
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-04-2014, 05:50 AM
  2. [SOLVED] excel vba 2010 BeforeClose event
    By junmacs in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-03-2014, 05:30 AM
  3. BeforeClose Event Help
    By trecie13 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2013, 04:27 PM
  4. BeforeClose event not firing?
    By bernmc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-06-2007, 03:54 AM
  5. How to get out of BeforeClose event without closing?
    By 42N83W in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-11-2005, 02:06 PM

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