Error message if any of 3 cells are blank

[AndyB63UK]

On a spreadsheet I have a "Save" button which saves the sheet in a specific place determined by the contents of 3 cells.
C4 = Date, E4 = Venue, I4 = Name
These three cells determine the location and name the file will be saved; c:\Venue\Year (from C4)\Month (from C4)\Name (a combination of day from C4 and name)

Can I add an IF statement in the code for the save button that pops up an error message if any 1 of the 3 cells are blank?

 

[jacqueslegrand]

Try

if range("C4").value="" or range("E4").value="" or range("I4").value="" then
msgbox ("The three cells must be saved
else
activeworkbook.saveas ...
end if

 

[slinky]

Presumably you're using VBA to drive the save operation?

If so, you could do something along the lines of IF LEN(Year) = 0 to drive a msgbox routine and an exit from the save process.

 

[jim may]

In the code (behind the Save Button) you could insert near the beginning the line: Call Foo
then add the below code (Foo) to your Standard module..

Sub foo()
Set Rng = Range("C4,E4,I4")
For Each C In Rng
    If C = "" Then
    MsgBox "Your Cell " & C.Address & " is Blank" & vbNewLine _
    & " Properly Enter data in missing area and re-try"
    Exit Sub
    End If
Next C
End Sub

 

[AndyB63UK]

Here's what's already behind the save button

Private Sub SaveSR_Click()'Save Show Report to G Drive




Dim MyPath  As String
Dim MyArr   As Variant
Dim pNum    As Long
Dim pBuf    As String
        
    On Error Resume Next


    With ActiveCell
        MyPath = "G:\Town Halls\Event Sheets" & "\" & [F4] & "\" & [M2] & "\" & [N2] & "\Show Reports"
    End With


    MyArr = Split(MyPath, "\")
    pBuf = MyArr(LBound(MyArr)) & "\"
    For pNum = LBound(MyArr) + 1 To UBound(MyArr)
        pBuf = pBuf & MyArr(pNum) & "\"
        MkDir pBuf
    Next pNum




Dim NewFile As String
Dim fName   As String
Dim MyDay As String
   
MyDay = [O2]
     If [O2] < 10 Then MyDay = "0" & [O2]


fName = Range("I4").Value
NewFile = MyPath & "\" & MyDay & " - " & fName & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFile, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
        :=False, OpenAfterPublish:=False
End Sub

 

[AndyB63UK]

Sorry, I had to split the date from C4 into year M2, Month N2 and Day O2 as I didnt know how to incorporate that in the code.

 

[jim may]

Private Sub SaveSR_Click()'Save Show Report to G Drive




Dim MyPath As String
Dim MyArr As Variant
Dim pNum As Long
Dim pBuf As String

On Error Resume Next

Call Foo 'this line tests your 3 cells

With ActiveCell
MyPath = "G:\Town Halls\Event Sheets" & "\" & [F4] & "\" & [M2] & "\" & [N2] & "\Show Reports"
End With

 

[AndyB63UK]

Thats great but it still continues with the rest of the code when you hit the OK button on the error message, and saves the file in the wrong place. ie if I leave all the cells blan I get a file G:\Town Halls\Event Sheets\1900\01 Jan\Show Reports\00 - .pdf

Is there a way to stop the save if there's an error?

 

[jim may]

At the very top of your standard module, in the declarations area you could enter: Dim Flag as Boolean

Then Modify my original code as follows:

Sub foo()
Set Rng = Range("C4,E4,I4")
For Each C In Rng
    If C = "" Then
    MsgBox "Your Cell " & C.Address & " is Blank" & vbNewLine _
    & " Properly Enter data in missing area and re-try"
    Flag = True
    Exit Sub
    End If
Next C
End Sub

then lastly.. in the code behind your Save button add the line:

Private Sub SaveSR_Click()'Save Show Report to G Drive




Dim MyPath As String
Dim MyArr As Variant
Dim pNum As Long
Dim pBuf As String

On Error Resume Next

Call Foo 'this line tests your 3 cells

If Flag = True
Exit Sub
End If

With ActiveCell
MyPath = "G:\Town Halls\Event Sheets" & "\" & [F4] & "\" & [M2] & "\" & [N2] & "\Show Reports"
End With

 

[AndyB63UK]

If Flag = True is showing as a syntax error