Display error message if a cell is left blank

  • Thread starter Thread starter Another Excel User
  • Start date Start date
A

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
 
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
 
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
 
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
 
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
 
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")
 
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
 
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.
 
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.
 
Back
Top