Popup Box / If Statement?

coreyjames

Board Regular
Joined
Apr 19, 2011
Messages
71
I am putting together a template file for my co-workers to use with their daily tasks. I work for a hospital and when we do our daily analyses a total of 24 hospitals should show up in our data. I would like a popup box to pop up on the screen when they try to save the file and their isnt a total of 24 hospitals.

So to clarify I have a formula that gives me a hospital count. If this count is less than 24 I want a pop up box to pop up on the screen when the user trys to save the file to let them know not all hospitals are shown. I will also have a comments box to the side of the hospital count. If there is a comment in the comment box I do not want the popup box to show up because the analyst has given a reason as to why it is not on the list.

If possible I would like the popup box to ask for the reason not all hospitals are on the list. I would like to be able to type the reason in the box and have it appear as a comment beside the hospital count.

Any help would be greatly appreciated. Thanks!
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Do you have a specific sheet and cell that the total goes into and is the comment in the same cell? If so you can use the Workbook Event BeforeClose.

Do you know much about VBA?
 

coreyjames

Board Regular
Joined
Apr 19, 2011
Messages
71
I am not that great at VBA. I usually find code on this message board and then tailor it for my needs.

the name of the workbook is "HospitalSummary"

the sum of the hospitals is in cell b10.

I would really like the pop up box to allow me to type in the comments and then put them in say cell d10. Is this possible?
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Try this out.

Create a New workbook and add a sheet name HospitalSummary then in B10 place in 23 or less.

Use Alt + F11 and this takes you into VBA, on the left double click on ThisWorkbook, next at the top where it shows you 2 drop downs select the one on the left and Change General to Workbook, then on the right change the Procedure to BeforeClose

Add in this code and test it, save the workbook with a dummy name, close the VBA screen down and then close the book. You will get a prompt to type something in.

Save after.

Next open the workbook clear the comment out and change the cell value of B10 to 24 and save and close and you shouldn't get the prompt.

Post back once tested then it is a case of working on the Comment which has a text value.

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)<br><SPAN style="color:#00007F">If</SPAN> Sheets("HospitalSummary").Range("B10") < 24 <SPAN style="color:#00007F">Then</SPAN><br>Sheets("HospitalSummary").Range("d10") = InputBox("Please state why not 24", "Sample")<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 

coreyjames

Board Regular
Joined
Apr 19, 2011
Messages
71

ADVERTISEMENT

When I try to exit the document I get an error and a yellow line highlights this line.


If Sheets("HospitalSummary").Range("B10") < 24 Then

I am using excel 2007.

Also would this work as the beforesave procedure instead of beforeclose?
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,701
Office Version
  1. 2016
Platform
  1. Windows
Do you have your macros enabled?

This is better going in the BeforeClose Event.

Have you checked the sheet name?
 

coreyjames

Board Regular
Joined
Apr 19, 2011
Messages
71
Trevor. Your a genius! It is working well. So how do we implement the comment portion?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,660
Messages
5,626,148
Members
416,165
Latest member
hamburger138

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top