• 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...

  • 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.

Frncis

Member
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:
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.
 

Hui

Excel Ninja
Staff member
Frncis

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

Frncis

Member
Frncis

Can you please attach the file so we can see what is going on here?
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.
 

Frncis

Member
Save it as a Excel Binary File type
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.
 

Frncis

Member
I still get the message"file too big".

I am closer to a solution. I stepped through the code & found that I had call OVR_MEETING_1_Close twice. That is why when you clicked NO it would stop the macro & if you clicked a second time it, it closed. Here is a current version of the code. The only problem is that if NO is clicked it closes the workbook.
Code:
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
Ok2Close = True
Application.Quit
Ok2Close = False
ThisWorkbook.Close SaveChanges:=True

End Select
End Sub
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:

Hui

Excel Ninja
Staff member
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
 

Frncis

Member
ok. I will do this later today (10/15/18)
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:
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
 

Frncis

Member
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:
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.
 

Frncis

Member
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
 

Frncis

Member
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
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.
 

Frncis

Member
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:
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:
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".
 
Top