PDA

View Full Version : Msg Box if cell left blank



Chris2460
09-07-2016, 01:59 PM
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
09-07-2016, 03:07 PM
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
09-08-2016, 01:11 AM
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
09-08-2016, 11:07 AM
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......