Data Validation for empty cell

[dan1130]

Need your help please..

I have a form that requires an approval. The problem is that the approval is always missed.

I want to place a validation if cell is empty and also have the error alert pop up.

 

[lenze]

Hi and welcome to the board!!!
You can use a BeforeSave event to check that cell. An example

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If Sheet1.Range("$A$1") = "" Then
   MsgBox "Approval required"
   Cancel = True
   Sheet1.Range("$A$1").Select
End If
End Sub

Place this in the ThisWorkBook module!!
HTH
lenze

 

[dan1130]

Hi Lenze,

Thanks for the quick reply but that's way over my head.

I do not know where to place the code. I'm assuming it's the VB editor.

I don't mind trying it just do no know where to place it.

Thanks
Daniel

 

[lenze]

Open the VBE(ALT+F11). You should see the Project Explorer on the left!! If not, enter CTRL+R to open it. DoubleClick on the module "ThisWorkBook" and paste the code I posted in the white panel. Change the SheetNanme and cell reference for your desired "Check Off" cell!I t's really quite easy. The code will run whenever you save the file. You may want to go with a sightly different code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheet1.Range("$A$1") = "" Then
MsgBox "Approval required"
Cancel = True
Sheet1.Range("$A$1").Select
End If
End Sub

It might be less confusing for making backup saves

Good luck
lenze

 

[dan1130]

Hi Lenze,

I tried it this morning and I get a run time error "424" message. I click debug and it highlights the bolded line below.

Cell F426 is merged, so I took the merge off and I still get the message. I have 3 tabs in this workbook and this approval box is in the tab named "Invoice".

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Invoice.Range("$F$426") = "" Then
MsgBox "Approval required"
Cancel = True
Invoice.Range("$F$426").Select
End If
End Sub

 

[lenze]

In my example, I used the CodeName for the sheet. You are trying to use the "Assigned Name" It's a slightly different syntax!

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Sheets("Invoice").Range("$F$426") = "" Then
MsgBox "Approval required"
Cancel = True
Sheets("Invoice").Range("$F$426").Select
End If
End Sub

See my discussion on Sheet Names here

lenze<!-- / message -->