Stop Macro Running If Cell is empty and highlight empty cells in red

Question

I am trying to run a macro that records data put into a form.

When the macro is run, it copies the information to a worksheet called Log sheet. It then clears the form for the next user to submit their details.

I would like the macro to only run once the required fields have been filled in Cells (B3,B4,B5,F3,F4,G19,B30,F30). If any of the cells are blank, then do not copy the data to the next page but add Msgbox "Complete the required fields" and highlight the empty cells in red to be completed.

The macro works for recording details, but I do not know how to prevent it from running if the above cells are not filled in. It currently shows Compile Error: Else without If.

Sub Submission()

    If Range("B3") = "" Then
      MsgBox "Please insert Outlet Name"
      Range("B3").Select
    End If
    If Range("B4") = "" Then
      MsgBox "Please insert Date"
    End If
    If Range("F3") = "" Then
      MsgBox "Please insert Till Number"
      Range("F3").Select
    End If
    If Range("F4") = "" Then
      MsgBox "Please insert Operator Name"
    End If
    If Range("B5") = "" Then
      MsgBox "Please insert Department Name"
    End If
    If Range("G19") = "" Then
      MsgBox "Please insert Till Reading"
    End If
    If Range("B30") = "" Then
      MsgBox "Please insert Counted By Name"
    End If
    If Range("F30") = "" Then
      MsgBox "Please insert Witnessed By Name"
    Else
      Sheets("Input ").Select
      Sheets("Log Sheet").Visible = True
      Sheets("Log Sheet").Select
      Sheets("Sheet3").Visible = True
      Sheets("Log Sheet").Select
      Rows("2:2").Select
      Selection.Copy
      Application.Goto Cells(Rows.Count, "A").End(xlUp).Offset(1), Scroll:=True
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
      Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
      Sheets("Input ").Select
      Range("B3:C3").Select
      Selection.ClearContents
      Range("F3:H3").Select
      Selection.ClearContents
      Range("F4:H4").Select
      Selection.ClearContents
      Range("B4:C4").Select
      Selection.ClearContents
      Range("B5:C5").Select
      Selection.ClearContents
      Range("C7").Select
      Selection.ClearContents
      Range("C8").Select
      Selection.ClearContents
      Range("C9:C17").Select
      Selection.ClearContents
      Range("G19:H19").Select
      Selection.ClearContents
      Range("C6").Select
      Selection.ClearContents
      Range("A24:H28").Select
      Selection.ClearContents
      Range("C21").Select
      Selection.ClearContents
      ActiveWindow.SmallScroll Down:=9
      Range("B30:C30").Select
      Selection.ClearContents
      Range("F30:H30").Select
      Selection.ClearContents
      Range("C19").Select
      Selection.ClearContents
      Range("C20").Select
      Selection.ClearContents
      Range("B3").Select
      Range("K6:K17").Select
      Selection.ClearContents
      Sheets("Log Sheet").Select
      ActiveWindow.SelectedSheets.Visible = False
      Sheets("Sheet3").Select
      ActiveWindow.SelectedSheets.Visible = False
      Range("B3:C3").Select
      Sheets("Input ").Select
      Range("B3:C3").Select
      ActiveWorkbook.Save
End Sub

All data validation should be done on the form before it is copied to the worksheet. ie, if textbox2 = "" then (add commands to get the user to fill the form properly.) That way you know the worksheet has correct info and you won't need to validate it on the worksheet. Can you update your question by showing the code in your form module? — John Muggins, May 16 '17 at 20:12
1) To fix compile error, add `End If` right before `End Sub`. To stop it from running if a value is missing place `Exit Sub` right after the `Msgbox` for each condition 3) Read about [how to avoid select](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) and your programming life will be much better. — Scott Holtzman, May 16 '17 at 20:21

Steve Lovell · Accepted Answer · 2017-05-16T21:59:32.170

0

I've made some tweaks to your code ... several more are possible, but this will get you started. Firstly, I've replaced your End If / Ifs with ElseIfs. This allows the final Else to be triggered only if no other branch is triggered. I've also removed all the unncessary selects which slow down the code.

Sub Submission()

    If Range("B3") = "" Then
      MsgBox "Please insert Outlet Name"
      Range("B3").Select
    ElseIf Range("B4") = "" Then
      MsgBox "Please insert Date"
    ElseIf Range("F3") = "" Then
      MsgBox "Please insert Till Number"
      Range("F3").Select
    ElseIf Range("F4") = "" Then
      MsgBox "Please insert Operator Name"
    ElseIf Range("B5") = "" Then
      MsgBox "Please insert Department Name"
    ElseIf Range("G19") = "" Then
      MsgBox "Please insert Till Reading"
    ElseIf Range("B30") = "" Then
      MsgBox "Please insert Counted By Name"
    ElseIf Range("F30") = "" Then
      MsgBox "Please insert Witnessed By Name"
    Else
      Sheets("Log Sheet").Visible = True
      Sheets("Sheet3").Visible = True
      Sheets("Log Sheet").Rows("2:2").Copy
      With Cells(Sheets("Log Sheet").Rows.Count, "A").End(xlUp).Offset(1)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      End With
      With Sheets("Input ")
      .Range("B3:C5").ClearContents
      .Range("F3:H4").ClearContents
      .Range("C6:C17").ClearContents
      .Range("C19:C20").ClearContents
      .Range("G19:H19").ClearContents
      .Range("A24:H28").ClearContents
      .Range("B30:C30").ClearContents
      .Range("F30:H30").ClearContents
      .Range("K6:K17").ClearContents
      End With
      Sheets("Log Sheet").Visible = False
      Sheets("Sheet3").Visible = False
      ActiveWorkbook.Save
    End If
End Sub

edited May 16 '17 at 21:59

answered May 16 '17 at 20:32

Steve Lovell

2,564
2
13
16

Thanks Steve Lovell - This works with the prompts but now seems to paste the data on the Input Sheet and not on the next available blank row in the log sheet worksheet. Apologies but I am still learning to read the code. – Shayne May 16 '17 at 20:57
I see what I did ... have updated. Hopefully that's now fixed. – Steve Lovell May 16 '17 at 21:05
Thanks very much for the help with this. :) – Shayne May 16 '17 at 21:10
No worries. I've made a few additional minor tweaks. Please mark my answer as accepted if you feel the question is now answered. P.S. I recommend ConditionalFormatting for your cell highlighting. – Steve Lovell May 16 '17 at 21:18
You could probably also get rid of the `Visble = True/False` lines also. – Steve Lovell May 16 '17 at 21:19
Thanks Steve. It still copies back on to the Input sheet but I replaced the copy and paste code with my original code and it now works fine. Quicker with your changes too!! Thanks again – Shayne May 16 '17 at 21:27
Sorry about that, I'd worked with different sheet names at my end to test the code, and forgot to change that one back. I did now, so I think it should work as I've got it. Either way, glad you got it working. – Steve Lovell May 16 '17 at 22:01

Stop Macro Running If Cell is empty and highlight empty cells in red

1 Answers1