Validate a Cell with VB excel

[portucale]

Hi,

I am looking for a VB statement which a cell can't be left blank, if is blank a warning message would be displayed and dind't let the user finnish the work, I've tried;
If IsNull(Value) Then
a = MsgBox("Please enter the reference number for this Monitoring, Thanks.")
Exit Sub
End If

but is not working, please any ideas would be very much appreciated.

Thanks,

 

[lenze]

Use the Before_Save Event in the ThisWorkbook module

If Range("$A$1") = "" Then
      msgbox " You must enter a value in A1"
      Cancel = True
      Range("$A$1").Select
End If

lenze

 

[Datsmart]

You can also use an InputBox to allow the user to fill in the cell value.

Sub EmptyCell()
    If ActiveCell.Value = "" Then
     varUserInput = InputBox("Please enter the reference number for this Monitoring", _
      "Need Ref#", "")
     If varUserInput <> "" Then ActiveCell.Value = varUserInput
    End If
End Sub

Note I used activecell, Lenze assigned cell to A1, adjust as desired.

 

[portucale]

Hi,

Thanks to both, tried the first one but for some reason it allow to cary on and complete the execution, the (Cancel = True) for some reason gives the "OK" button, however I think I have resolved the issue I used the following statement;

Value = s1.Cells(7, 8)
If IsEmpty(Value) Then
a = MsgBox("Please enter a Reference number, Thanks.")
Range("$H$7").Select
Exit Sub
End If
I will be trying the second option also, as I am very new to the VB code.

Thanks again for the help, which is always very much appreciated.

Regs,