1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Yes no message box option not working as expected.

Discussion in 'VBA Macros' started by Frncis, Oct 9, 2018.

  1. Frncis

    Frncis Member

    Messages:
    61
    I had a user that would not use the exit active X button, that the code below was created, 2 friends helped me with this code.
    Code (vb):
    Public Ok2Close As Boolean

    Sub CloseMacro()
    Call OVR_MEETING_1_Close
    'Prevents closure of application by clicking on application X.
    'Second half of code found on Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Cancel = Not Ok2Close
    Call OVR_MEETING_1_Close
      Ok2Close = True
      ThisWorkbook.Close SaveChanges:=True
            Application.Quit
        Ok2Close = False
    End Sub

    Sub OVR_MEETING_1_Close()
    'This code does the following tasks.

      '1.  Asks if you want the application closed.
       
      '2.  Returns the application to the home page (If yes).

      '3.  Does any final calculations (If Yes).

      '4.  Closes both the work book & the application (if yes).

    Dim Msg As String, Ans As Variant
     
        Msg = "Would you like to close Career Link meeting List?"
     
        Ans = MsgBox(Msg, vbYesNo, "Voc. Rehab. - Career Link")
     
        Select Case Ans
         
        Case vbYes
            Dim i As Long
     
        Application.ScreenUpdating = False

        For i = 1 To ThisWorkbook.Sheets.Count
            Application.Goto Reference:=Sheets(i).Range("A1"), Scroll:=True
        Next i

        Application.ScreenUpdating = True
     
        Sheets("TOC").Select
    Application.Calculation = xlCalculationAutomatic
    Ok2Close = True
    Application.Quit
    Ok2Close = False
    ThisWorkbook.Close SaveChanges:=True

    End Select
    End Sub
    If Yes is clicked, workbook/application closes.
    If NO is clicked, workbook/application does not close, but does not allow data entry.
    If clicked a second time the workbook/application closes.
    I am trying allow the user to enter data if the No is chosen.
    I have googled & have not found this problem listed or a solution.
    I believe it has to do with the No response, but I am not certain about the proper wording.
  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,667
    Frncis

    Can you please attach the file so we can see what is going on here?
  3. Frncis

    Frncis Member

    Messages:
    61
    I tried & the file is too large. Any advise?
  4. Frncis

    Frncis Member

    Messages:
    61
    I tried to attach a file copy & it was to large. I tried a smaller file with just the code I am questioning & there were other issues, different from the original.
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,667
    Save it as a Excel Binary File type
  6. Frncis

    Frncis Member

    Messages:
    61
    I still get the message"file too big". I was wondering. Is there code to stop the macro when no is clicked? The yes works fine. Also should I use different code? This code works fine in workbooks where I am not trying to prevent the user from using X to close the workbook. I have tried various text such as end, etc., which caused other problems.
  7. Frncis

    Frncis Member

    Messages:
    61
    I know there is something I am missing to prevent the macro from going to the next I. When I step through the code it stops @ select case ans, & the Yes/No message comes up. I No is clicked ii closes.
    Last edited: Oct 11, 2018
  8. Hui

    Hui Excel Ninja Staff Member

    Messages:
    11,667
    Can you put it in a Dropbox or One drive folder and share a link to it
    Make sure it is set for public access?

    We have I think a 2MB file size limit
  9. Frncis

    Frncis Member

    Messages:
    61
    ok. I will do this later today (10/15/18)
  10. Frncis

    Frncis Member

    Messages:
    61
    Here is the one drive link

    https://1drv.ms/x/s!Ak-4iXjPpsJMfh2pruCg-XzvoJY

    I was having another problem with the file (related to calendar pop up), but that issue is resolved. The only problem now, is having VBA stop when no is chosen. Currently it runs through the entire code in that macro. I fixed the problem with double clicking of NO. This was part of the original code problem. Here is the current code.

    Code (vb):
    Public Ok2Close As Boolean

    Sub CloseMacro()
    Call OVR_MEETING_1_Close
    'Prevents closure of application by clicking on application X.
    'Second half of code found on Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Cancel = Not Ok2Close

      Ok2Close = True
      ThisWorkbook.Close SaveChanges:=True
            Application.Quit
        Ok2Close = False
    End Sub

    Sub OVR_MEETING_1_Close()
    'This code does the following tasks.

      '1.  Asks if you want the application closed.
       
      '2.  Returns the application to the home page (If yes).

      '3.  Does any final calculations & saves (If Yes).

      '4.  Closes both the work book & the application (if yes).

    Dim Msg As String, Ans As Variant
       
        Msg = "Would you like to close Career Link meeting List?"
       
        Ans = MsgBox(Msg, vbYesNo, "Voc. Rehab. - Career Link")
       
        Select Case Ans

        Case vbYes
            Dim i As Long
       
        Application.ScreenUpdating = False
       
        For i = 1 To ThisWorkbook.Sheets.Count
            Application.Goto Reference:=Sheets(i).Range("A1"), Scroll:=True
     
       
        Next i
       
        Application.ScreenUpdating = True

        Sheets("TOC").Select
    Application.Calculation = xlCalculationAutomatic
    Application.Quit
    ThisWorkbook.Close SaveChanges:=True

    End Select
    End Sub
  11. Frncis

    Frncis Member

    Messages:
    61
    I found the original code that does work as expected. I can't see any difference between the 2 codes from the line that starts with Dim msg....... to the line that ends with ........End Select Here is a copy of the working code.

    Code (vb):
    Sub OVR_MEETING_1_Close()
    Dim Msg As String, Ans As Variant
       
        Msg = "Would you like to close OVR meeting List?"
       
        Ans = MsgBox(Msg, vbYesNo, "Voc. Rehab - OVR Data Entry")
       
        Select Case Ans
           
        Case vbYes
            Dim i As Long
       
        Application.ScreenUpdating = False
       
        For i = 1 To ThisWorkbook.Sheets.Count
            Application.Goto Reference:=Sheets(i).Range("A1"), Scroll:=True
        Next i
       
        Application.ScreenUpdating = True

        Sheets("TOC").Select
    Application.Calculation = xlCalculationAutomatic
    Application.Quit
    ThisWorkbook.Close SaveChanges:=True

    End Select
    End Sub
    I did copy & past into the test file, & it does not work.
  12. Frncis

    Frncis Member

    Messages:
    61
    I found the problem . these two lines do not play well with the other lines of code. Problem #1 Ok2Close = True, problem #2 Ok2Close = False These 2 lines are found in module 1. Problem #3 this line is in The workbook. Cancel = Not Ok2Close. This presents another problem, the problem of preventing the application from being closed by clicking on the X. There is a user that uses the X to close the application, instead of clicking an activex button that closes the wokbook & application. Using the X causes a whole bunch of other problems.
    Now that I know the problem, I will start working on a solution, but definitely welcome assistance.
    I have posted a work sample of the work book on one drive.

    https://1drv.ms/x/s!Ak-4iXjPpsJMfh2pruCg-XzvoJY
  13. Frncis

    Frncis Member

    Messages:
    61
    I tried several lines of code & they do prevent the workbook/application from closing using the X. However When the exit button is clicked yes, it prevents the application from closing.
  14. Frncis

    Frncis Member

    Messages:
    61
    The problem solved. I tested it several times & it does work. Please review to see if there are any errors that you can find. There are 2 parts to the solution. Part 1 is located in Thisworkbook.
    Code (vb):
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    'Part of code to call a pop up calendar.
     On Error Resume Next
              Application.OnKey "+^{C}"
        Application.CommandBars("Cell").Controls("Insert Date").Delete
        'Prevents closure by clicking on the Red X.
       Cancel = Not Ok2Close
    End Sub
    The other part is in module 1.

    Code (vb):
    Public Ok2Close As Boolean
    Sub OVR_MEETING_1_Close()
    'This code does the following tasks.

      '1.  Asks if you want the application closed.
         
      '2.  Returns the application to the home page (If yes).

      '3.  Does any final calculations & saves (If Yes).

      '4.  Closes both the work book & the application (if yes).

    Dim Msg As String, Ans As Variant
       
        Msg = "Would you like to close Career Link meeting List?"
       
        Ans = MsgBox(Msg, vbYesNo, "Voc. Rehab - Career Link Data Entry")
       
        Select Case Ans
           
        Case vbYes
            Dim i As Long
       
        Application.ScreenUpdating = False
       
        For i = 1 To ThisWorkbook.Sheets.Count
            Application.Goto Reference:=Sheets(i).Range("A1"), Scroll:=True
        Next i
       
        Application.ScreenUpdating = True

        Sheets("TOC").Select
    Application.Calculation = xlCalculationAutomatic
    ThisWorkbook.Close SaveChanges:=True
      Ok2Close = True
      Application.Quit
    End Select
    End Sub
    I just had to play around with these 2 pieces of code. " Ok2Close = True Application.Quit".

Share This Page