Some versions of excel will fire that worksheet_deactivate event (but others
won't).

But there is another worksheet event you could try:

Option Explicit
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim RngToCheck As Range
Set RngToCheck = Me.Range("b5:b45")
If Application.CountA(RngToCheck) < RngToCheck.Cells.Count Then
MsgBox "You have left a required field empty"
Me.Select
End If
End Sub

You may want to keep both events.

Another Excel User wrote:

Thanks Dave, but is there anyway I can tie this into a hyperlink because
currently I have a hyperlink that allows the user to advance to the next
sheet, so what I would like is for the hyperlink to work like a command
button, once they click on the hyperlink the error message would appear.

Once again, thank you so much for your help so far.

"Dave Peterson" wrote:

B5:B50 is 46 cells.
If Application.CountA(Me.Range("B5:B50")) < 46 Then

To keep me from having to do arithmetic, I'd use:

Option Explicit
Private Sub Worksheet_Deactivate()
Dim RngToCheck As Range
Set RngToCheck = Me.Range("b5:b45")
If Application.CountA(RngToCheck) < RngToCheck.Cells.Count Then
MsgBox "You have left a required field empty"
Me.Select
End If
End Sub

==
Actually, I wouldn't use this kind of thing.

I'd use formulas like this in adjacent cells:
=if(b5="","","<-- Please enter a value here")




Gord Dibben wrote:

I would go with sheet_deactivate code in Sheet1 then it would not matter which
"next sheet" user selected.

Private Sub Worksheet_Deactivate()
If Application.CountA(Me.Range("B5:B50")) < 45 Then
MsgBox "You have left a required field empty"
Sheets("Sheet1").Activate
End If
End Sub

Gord

On Mon, 11 Feb 2008 15:40:49 -0000, "Sandy Mann"
wrote:

In the Sheet module paste in this code:

Private Sub Worksheet_Activate()
If Application.CountA(Sheets("Sheet1").Range("B5:B50" )) < 45 Then
MsgBox "You have left a required field empty"
Sheets("Sheet1").Activate
End If
End Sub

If you want the user to be able to porceed even with a blank in a cell
remove the

Sheets("Sheet1").Activate

line.

--

Dave Peterson


--

Dave Peterson