Excel – VBA – Verify a cell is NOT empty and NOT save Workbook if True

SCENARIO

I have a validation drop down box with cities listed. How do I make it so if they forget to use the drop down and put the city in cell A9 they get a message stating, "Must add city"?

It's like I need two validation rules but excel only lets me use one per cell. In this case my list box in A9. The goal is to make sure they add something (no blanks).

Also is there a way in VB to state if certain cells are not filled, it will NOT save the worksheet?

Someone told me to use the Workbook_BeforeSave event but I don't know how to get there or what to put into it.

I did get the VB toolbar up and I got the Worksheet editor, but I can't find the Workbook editor.

SOLUTION

Hi, regarding the validation list:

1. Right click on Worksheet tab where the A9 cell is.
2. Select View Code;
3. Paste the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$A$9" Then
       If Target = "" Then MsgBox ("You must select a value from the list")
   End If
End Sub

Regarding the second question, assuming A9 again assuming the Sheet name is Sheet1:

1. While you are in the VBA Editor. On the left side window, double-click ThisWorkBook and then paste in the code sheet area:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   If Sheets("Sheet1").Range("$A$9") = "" Then
       MsgBox ("You cannot leave this cell blank")
       Sheets("Sheet1").Range("$A$9").Select
       Cancel = True
   End If
End Sub

Excel – VBA – Verify a cell is NOT empty and NOT save Workbook if True

SOURCE

LINK (En.allexperts.com)

LANGUAGE

ENGLISH