Display error message if a cell is left blank

[Another Excel User]

How do you get Excel to display an error message if a cell is left blank?

I have a workbook with multiple sheets and what I want to do is for Excel do
to is look at a certain range and display an error message if a cell within
the defined range is left blank. I want this message to appear when someone
tries to advance to the next sheet.

For example: In Sheet 1 have a row of questions cells A5:A50 and a place to
answer those questions B5:B50 and when I am finished with answering the
questions I have to click on the next sheet, but in case I forgot to answer a
question (leaving one of the B cells empty) I would like Excel to display an
error message that says something like "you have left a required field empty,
are you sure you want to continue"

Thank you in advance for your help

 

[Pete_UK]

Assuming you use all the cells in B5:B50 for answers, then you could
try something like this:

="You have answered "&TEXT(COUNTA(B5:B50,"0")&"out of 46 questions"

Hope this helps.

Pete

 

[Sandy Mann]

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.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

I meant of course In the Sheet2 module.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Gord Dibben]

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

 

[Dave Peterson]

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")

 

[Sandy Mann]

You know I had never even noticed that there was a deactivate option there!

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Another Excel User]

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]

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.