Popup Box / If Statement?

[coreyjames]

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!

 

[Trevor G]

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]

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]

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]

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]

Do you have your macros enabled?

This is better going in the BeforeClose Event.

Have you checked the sheet name?

 

[coreyjames]

Trevor. Your a genius! It is working well. So how do we implement the comment portion?