Msg Box if cell left blank

[Chris2460]

Hello all,

I have this working macro:

Option Explicit
Sub SaveAsExample()
Dim FName As String
Dim FPath As String
FPath = "z:\i Wing\Names"
FName = Format$(Date, "dd-mm-yyyy") & " " & Sheets("Sheet1").Range("H12").Text
ThisWorkbook.SaveAs Filename:=FPath & "" & FName
End Sub

I would like to be able have a msg box if cell D48 has been left bank when the user tries to save, the messgae text is irrelevant... would this need to be a whole new macro or could it be included above: any thoughts...

[Leith Ross]

Hello Chris2460,

Try this...

Option Explicit


Sub SaveAsExample()


Dim FName As String
Dim FPath As String


If Sheets("Sheet1").Range("D48") <> "" Then
    FPath = "z:\i Wing\Names"
    FName = Format$(Date, "dd-mm-yyyy") & " " & Sheets("Sheet1").Range("H12").Text
    ThisWorkbook.SaveAs Filename:=FPath & "" & FName
Else
    MsgBox "Some Data is Missing.", vbExclamation, "Workbook Not Saved"
End If
End Sub

[Chris2460]

Mmmmm, this saved as (Names08-09-2016)...... Do I need to create a seperate macro and then try to get the missing cell macro to run first followed by the Saveas example., along the lines of:

Sub Msgbox
If Sheets("Sheet1").Range("D48") <> "" Then 
MsgBox "Some Data is Missing.", vbExclamation, "Workbook Not Saved" 
EndSub

I know this is not correct but you get the idea................. (Ihope) Cheers

[Chris2460]

Sorry I might have posted this twice, I am getting there with the msg box, this now works perfect for me:

Sub CheckIfCellIsEmpty()
Dim isMyCellEmpty As Boolean
isMyCellEmpty = IsEmpty(Range("D48"))
If isMyCellEmpty = True Then
MsgBox "Intelligence Report Requires Completing & the Box Selected"
End If
End Sub

What I now need to do to complete the project is get this to run prio / inconjunction with:

Sub Email()
'
' Email Macro
'
'
    Application.Dialogs(xlDialogSendMail).Show
    ActiveWorkbook.Save
    Application.Goto Reference:="Email"
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 4")).Select
    Selection.OnAction = "Email"
    Range("L60").Select
    ActiveWindow.SmallScroll Down:=-57
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Any thoughts......