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

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.

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")
       Cancel = True
   End If
End Sub

LINK (En.allexperts.com)


Comments are closed.