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

On Feb 11, 3:10*pm, Another Excel User <Another Excel
wrote:
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

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


Replace @mailinator.com with @tiscali.co.uk


"Another Excel User" <Another Excel wrote in
message ...
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

I meant of course In the Sheet2 module.

--
HTH

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


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
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


Replace @mailinator.com with @tiscali.co.uk


"Another Excel User" <Another Excel wrote
in message ...
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

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.

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

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


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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.

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

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