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

Display Personalized Message before exiting Excel

Pasadu

Member
1. Please Sir, before excel closes, i want a message to be displayed, "Please contact Prince on 0242425696."

2. When excel closes, i want the values in Cells A4, A7 and A10 to be set to 0.

I have attached the File. Thank You.
 

Attachments

  • Meridian Port Services 1.xlsm
    145.7 KB · Views: 5
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("A4") = 0
    Range("A7") = 0
    Range("A10") = 0
    MsgBox "Please contact Prince on 0242425696"
End Sub
 
Sir, thank you for the response but it did not work. The message did not display, the values did not change. I have attached a screenshot of how i placed the code. Thank You.
 

Attachments

  • vba.png
    vba.png
    63.4 KB · Views: 8
Works for me. Be sure that you place in the Workbook Events and not in the Worksheet events.
 

Attachments

  • Meridian Port Services 1.xlsm
    150.9 KB · Views: 13
Thank you sir, it worked yet the excel message of saving or not saving still pops up after the personalized message. Is it possible that that "save, don't save, cancel," message will not appear at all?
 
Add this line:
ActiveWorkbook.Save

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("A4") = 0
    Range("A7") = 0
    Range("A10") = 0
    MsgBox "Please contact Prince on 0242425696"
    ActiveWorkbook.Save
End Sub
I changed the way you use the msgboxes when you open the workbook.
Now the user has to press OK only once instead of 7 times.

Code:
Private Sub Workbook_Open()
    With Sheets("Sheet1")
        .Protect userinterfaceonly:=True
        .EnableSelection = xlUnlockedCells
    End With

    MsgBox "This is Meridian Port Services (MPS) Terminal's Calculation Data." _
    & vbCrLf & "Please INPUT the following:" _
    & vbCrLf & "The Quantity of Container(s) to be Cleared." _
    & vbCrLf & "If Examination, input the Quantity of Container(s) to be Examined." _
    & vbCrLf & "If Rent Charges, Input the Vessels' Discharge Date(s) and the Date(s) that the Container(s) will be Loaded." _
    & vbCrLf & "Where there are more than 1 Container for RENT-Related issues, every Container info should be recorded on a new line." _
    & vbCrLf & "Thank You."
End Sub
 
Thank You. The intro worked but forcing the workbook to close without the "saving" pop up messages not to appear, keeps on appearing.
 
Did you do as I told?

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Range("A4") = 0
    Range("A7") = 0
    Range("A10") = 0
    MsgBox "Please contact Prince on 0242425696"
    ActiveWorkbook.Save
End Sub
 
Last edited by a moderator:
Thank you #6, #8 worked just as i wanted. I have another query *if permissible*. How can i hide other worksheets without anyone find out out about them even if they try to unhide them, they wont see anything.
 
Back
Top