Message Box before Closing file

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
I have a file that I send out to users on a monthly basis, and there are a few key cells that some controllers leave blank.

Is there a code that if, lets say, cell D8 is blank, that before they close the file, a message box pops up and says a brief message to them as a reminder to fill that cell in, and to make sure that they have filled in all variance explanations?

And if they press ok, it copies and pastes the name in Cell D8 from the active page, and selects a sheet in the workbook called "TitlePage", and pastes the name and current date and time (value only so it doesn't change) into Cell E47 on the title page?
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,001
Office Version
  1. 365
Try this in the ThisWorkbook module in VBA:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim my_string As String
If [D8] = "" Then
MsgBox "You cannot save this file until you fill in cell D8."
Cancel = True
End If
my_string = [D8] & " " & Format(Now, "h:mm")
Sheets("Title Page").[E47] = my_string
End Sub
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
Hi guys,

This code would help me a lot if I could modify it slightly.

Is it possible to change it so that when we try running another macro it checks cell K10 for data, and if it's empty to bring up the message saying, "You cannot proceed until you fill in the account number."

Also, if needed, can I add other cells to check after it's checked K10?

Thanks!
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
It doesn't seem to work.

I right clicked on the sheet I wanted the code, and pressed "view code". I pasted in the above code, and tested it, and it doesn't ask me for anything or copy and paste anything. I can save and close the file without it doing anything. Am I doing something wrong?
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467

ADVERTISEMENT

Hello again,

I've figured out parts of the code but even if there's nothing in the cells it still carries on with the macro after.

This is my code so far.
Code:
Sub test()
Dim his_string As String
Dim my_string As String
If [D8] = "" Then
MsgBox "You proceed until you fill in cell D8."
Cancel = True
End If
If [K10] = "" Then
MsgBox "You proceed until you fill in cell K10."
Cancel = True
End If
his_string = [D8] 
my_string = [K10]
Sheets("Title Page").[E47] = his_string
Sheets("Title Page").[f47] = my_string
End Sub

and it's activated by another macro.

Code:
Sub pp()
    Call test
    Sheets("Title Page").Select
    Range("C23,D24,C25,E25,E23,F24,G23,G25,H24,I23,I25").Select
    Range("I25").Activate
    With Selection.Interior
        .ColorIndex = 6
        .Pattern = xlSolid
    End With
End Sub

So when I use the pp sub, it activates the test sub and the message boxes appear if the cells are empty, but even though they are empty it carries on with the rest of the macro.
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
gottimd said:
It doesn't seem to work.

I right clicked on the sheet I wanted the code, and pressed "view code". I pasted in the above code, and tested it, and it doesn't ask me for anything or copy and paste anything. I can save and close the file without it doing anything. Am I doing something wrong?

On the left hand side you'll notice a list of your worksheets. If you double-click on the "ThisWorkbook" part and paste your code in there it will work, but you need to change the "Title Page" bit is the name of your sheet.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,001
Office Version
  1. 365

ADVERTISEMENT

gottimd said:
It doesn't seem to work.

I right clicked on the sheet I wanted the code, and pressed "view code". I pasted in the above code, and tested it, and it doesn't ask me for anything or copy and paste anything. I can save and close the file without it doing anything. Am I doing something wrong?

It needs to go in the ThisWorkbook module, not in the Sheet module.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,001
Office Version
  1. 365
Ste_Moore01:

Instead of "Cancel = True", try "Exit Sub".
 

gottimd

Well-known Member
Joined
Jul 29, 2002
Messages
501
Ok, works now, thanks. How do I change the Date and Time format to say the Time with AM and PM and the Month-Date and Year? I tried:

Format(Now, "h:mm", "AMPM", "MM:DD:YYYY")
 

Ste_Moore01

Active Member
Joined
Jul 13, 2005
Messages
467
That's perfect!

Thanks Oaktree, and thanks gottimd for asking about this as I would never have thought about it!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,683
Messages
5,637,780
Members
416,982
Latest member
lisam77

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