I have a workbook where I would like to have a warnig message pop up on close if the value of cell E9, which is a formula to calculate whether it's in hours or kms, is in hours. The value in E9 will probably not change at all while the workbook is open.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
VBA Warning Message on Close When Cell Value is
0
Discussion
Hi RSWM
That should be easy to do but what do you mean by "..cell E9, which is a formula, equals hours."? A number of hours, the text "hours"?
A file nearly alsways helps me to understand- please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data (and any macros). Then one of us should be able to give specific help.
That should be easy to do but what do you mean by "..cell E9, which is a formula, equals hours."? A number of hours, the text "hours"?
A file nearly alsways helps me to understand- please edit your original question and use the Add Files... button to upload a representative Excel file (without any personal data) to show your existing data (and any macros). Then one of us should be able to give specific help.
John_Ru (rep: 6142) Oct 18, '23 at 12:36 pm
I have uploaded the file. Thank you
RWSM (rep: 10) Oct 18, '23 at 12:48 pm
Thanks for adding the file.
E9 could by "Hours" on both sheets "Nov 5" and "Nov 20"- on which sheets do you want to give the popup? Or is the current day's worksheet (e.g. "Oct 18" today)?
E9 could by "Hours" on both sheets "Nov 5" and "Nov 20"- on which sheets do you want to give the popup? Or is the current day's worksheet (e.g. "Oct 18" today)?
John_Ru (rep: 6142) Oct 18, '23 at 1:08 pm
Depending on what vehicle they choose, E9 will either be hours or kms for all sheets. I want the popup to come up when they go to close it to remind them that equipment needs to be in hours. popup would be on the active sheet.
RWSM (rep: 10) Oct 18, '23 at 1:22 pm
Answers
0
Selected Answer
Hi RSWM
In the attached file, I've added this event macro (under the Workbook object in VB Project Explorer):
Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Range("E9").Value = "HOURS" Then
r = MsgBox(ActiveSheet.Name & " cell E9 read 'HOURS', correct things before saving?", vbYesNo, "E9 should read KM")
If r = vbYes Then Cancel = True
End If
End SubThe bit in bold sees if E9 is "HOURS" and, if so, gives the user a popup and the chance to fix things.
I also added another similar event which triggers when a user moves to another worksheet- it tells them E9 was HOURS and if the chose Yes, goes back to that sheet. Here it is:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh.Range("E9").Value = "HOURS" Then
r = MsgBox(Sh.Name & " cell E9 read 'HOURS', go back to correct things?", vbYesNo, "E9 should read KM")
If r = vbYes Then Sh.Activate
End If
End SubYou could delete the latter if you'd rather not have such reminders.
Hope this fixes things for you. If so, please remember to mark this Answer as Selected.