Message Box before Closing file

[gottimd]

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?

 

[Oaktree]

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]

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]

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]

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.

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.

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]

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]

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]

Ste_Moore01:

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

 

[gottimd]

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]

That's perfect!

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