I'm using Excel 2000. I have a spreadsheet where a user enters numbers for
debits and credits. I'd like to have a popup appear when the user enters a
value incorrectly - ie, debits have to be entered as a positive number,
credits have to be negative.

Also, is there a way to erase or undo the previous entry?

I'm thinking the worksheet_change event is needed here, but what I've tried
doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
if ActiveCell.Offset(0, -1).Value = "Debit" and Range("ActiveCell.Value
<1 Then
MsgBox "The number entered needs to be positive"
Elseif ActiveCell.Offset(0, -1).Value = "Credit" and
Range("ActiveCell.Value 1 Then
MsgBox "The number entered needs to be negative"
End If
End Sub

Thanks for any and all help.



--
Craig

Hi Craig,

Assuming that the Debit and Credit entries are in different columns, why not
use Data Validation, with conditions of = 0 for debits and <= 0 for
credits.
If zero is not a valid option, simplify the condition to 0 and < 0.

Also, is there a way to erase or undo the previous entry?

If the undo buttion does not satisfy your needs, please add some
explanation.

---
Regards,
Norman



"Craig" wrote in message
...
I'm using Excel 2000. I have a spreadsheet where a user enters numbers
for
debits and credits. I'd like to have a popup appear when the user enters
a
value incorrectly - ie, debits have to be entered as a positive number,
credits have to be negative.

Also, is there a way to erase or undo the previous entry?

I'm thinking the worksheet_change event is needed here, but what I've
tried
doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
if ActiveCell.Offset(0, -1).Value = "Debit" and Range("ActiveCell.Value
<1 Then
MsgBox "The number entered needs to be positive"
Elseif ActiveCell.Offset(0, -1).Value = "Credit" and
Range("ActiveCell.Value 1 Then
MsgBox "The number entered needs to be negative"
End If
End Sub

Thanks for any and all help.



--
Craig

I think Data Validation is what you want. Example:

1) Select the cells that you want to allow only positive whole numbers
(Debits).
2) Select Data from the Worksheet Menu Bar
3) Select Validation
4) In the Allow window, select "Whole number"
5) In the Data window select "Greater than" or "Greater than or equal to"
6) In the Minimum window enter "0"
7) If you want to display a message when the user selects a cell in the
range then select the Input Message tab and enter an appropriate message
8) Select the Error Alert tab and select the desired Error Style, Title and
error message
9) Repeat for the Credit range except change selections as appropriate

Regards,
Greg

"Craig" wrote:

I'm using Excel 2000. I have a spreadsheet where a user enters numbers for
debits and credits. I'd like to have a popup appear when the user enters a
value incorrectly - ie, debits have to be entered as a positive number,
credits have to be negative.

Also, is there a way to erase or undo the previous entry?

I'm thinking the worksheet_change event is needed here, but what I've tried
doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
if ActiveCell.Offset(0, -1).Value = "Debit" and Range("ActiveCell.Value
<1 Then
MsgBox "The number entered needs to be positive"
Elseif ActiveCell.Offset(0, -1).Value = "Credit" and
Range("ActiveCell.Value 1 Then
MsgBox "The number entered needs to be negative"
End If
End Sub

Thanks for any and all help.



--
Craig

Well, good suggestions. However, what I need to do is something Data
Validation can't handle.

I have a scenario where the user can enter numbers ANYWHERE on the
spreadsheet (not 1 column for debits, 1 for credits). The client wants an
input page - so the user might enter the letters "DR" in cell A1, then 75,000
in cell B1, etc...something like this:

A1 B1
DR 75,000
DR 80,000
CR -75,000
CR -36,000
CR -56,000
CR -89,000
..
..
etc, etc...

So, you can see that if the user types a "DR", then the number had better be
positive, not negative. I realize that this may seem like an innefficient
way to do this. But it's set up the way the client wanted, etc. These
numbers to other spreadsheets, as well. So it's important that they're
entered correctly.

So, I'm back to where I started. I think I need to do this with VBA. Any
suggestions?

Thanks again.


--
Craig


"Greg Wilson" wrote:

I think Data Validation is what you want. Example:

1) Select the cells that you want to allow only positive whole numbers
(Debits).
2) Select Data from the Worksheet Menu Bar
3) Select Validation
4) In the Allow window, select "Whole number"
5) In the Data window select "Greater than" or "Greater than or equal to"
6) In the Minimum window enter "0"
7) If you want to display a message when the user selects a cell in the
range then select the Input Message tab and enter an appropriate message
8) Select the Error Alert tab and select the desired Error Style, Title and
error message
9) Repeat for the Credit range except change selections as appropriate

Regards,
Greg

"Craig" wrote:

I'm using Excel 2000. I have a spreadsheet where a user enters numbers for
debits and credits. I'd like to have a popup appear when the user enters a
value incorrectly - ie, debits have to be entered as a positive number,
credits have to be negative.

Also, is there a way to erase or undo the previous entry?

I'm thinking the worksheet_change event is needed here, but what I've tried
doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
if ActiveCell.Offset(0, -1).Value = "Debit" and Range("ActiveCell.Value
<1 Then
MsgBox "The number entered needs to be positive"
Elseif ActiveCell.Offset(0, -1).Value = "Credit" and
Range("ActiveCell.Value 1 Then
MsgBox "The number entered needs to be negative"
End If
End Sub

Thanks for any and all help.



--
Craig

Maybe something like this then:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False

If Target.Count = 1 And Target.Column 1 _
And IsNumeric(Target.Value) Then
If UCase(Target.Offset(0, -1).Value) = "DR" Then
If Target.Value < 0 Then
MsgBox "Debit must be positive"
Target.Value = ""
End If
ElseIf UCase(Target.Offset(0, -1).Value) = "CR" Then
If Target.Value 0 Then
MsgBox "Credit must be negative"
Target.Value = ""
End If
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right click the sheet tab, select View
Code and paste the code in there.

Hope this helps
Rowan

Craig wrote:
I'm using Excel 2000. I have a spreadsheet where a user enters numbers for
debits and credits. I'd like to have a popup appear when the user enters a
value incorrectly - ie, debits have to be entered as a positive number,
credits have to be negative.

Also, is there a way to erase or undo the previous entry?

I'm thinking the worksheet_change event is needed here, but what I've tried
doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
if ActiveCell.Offset(0, -1).Value = "Debit" and Range("ActiveCell.Value
<1 Then
MsgBox "The number entered needs to be positive"
Elseif ActiveCell.Offset(0, -1).Value = "Credit" and
Range("ActiveCell.Value 1 Then
MsgBox "The number entered needs to be negative"
End If
End Sub

Thanks for any and all help.

Rowan:

That looks like exactly what I'm looking for, except one problem: When I
cut and paste it into my worksheet, it doesn't work.

The entire Worksheet_Change event doesn't even trigger. In other words,
nothing happens at all.

Even when I set a step break to occur, it bypasses it. Don't know what to
do. Is there something that I need to do or "turn off" first (ie, rules,
etc.)??

Thanks.
--
Craig


"Rowan" wrote:

Maybe something like this then:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
Application.EnableEvents = False

If Target.Count = 1 And Target.Column 1 _
And IsNumeric(Target.Value) Then
If UCase(Target.Offset(0, -1).Value) = "DR" Then
If Target.Value < 0 Then
MsgBox "Debit must be positive"
Target.Value = ""
End If
ElseIf UCase(Target.Offset(0, -1).Value) = "CR" Then
If Target.Value 0 Then
MsgBox "Credit must be negative"
Target.Value = ""
End If
End If
End If
ErrorHandler:
Application.EnableEvents = True
End Sub

This is worksheet event code. Right click the sheet tab, select View
Code and paste the code in there.

Hope this helps
Rowan

Craig wrote:
I'm using Excel 2000. I have a spreadsheet where a user enters numbers for
debits and credits. I'd like to have a popup appear when the user enters a
value incorrectly - ie, debits have to be entered as a positive number,
credits have to be negative.

Also, is there a way to erase or undo the previous entry?

I'm thinking the worksheet_change event is needed here, but what I've tried
doesn't seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
if ActiveCell.Offset(0, -1).Value = "Debit" and Range("ActiveCell.Value
<1 Then
MsgBox "The number entered needs to be positive"
Elseif ActiveCell.Offset(0, -1).Value = "Credit" and
Range("ActiveCell.Value 1 Then
MsgBox "The number entered needs to be negative"
End If
End Sub

Thanks for any and all help.

Hi Craig

What have you done to test it? Bear in mind that this will not work on
data that is already on the sheet but rather any new value entered.

If you are sure you have pasted the code into the sheets code module,
enter DR in cell A1 and then -5000 in cell B1. If that doesn't work then
type
application.enableevents = true
in the VBE immediate window and hit enter.
Then try entering another -ve value in B1.

Regards
Rowan

Craig wrote:
Rowan:

That looks like exactly what I'm looking for, except one problem: When I
cut and paste it into my worksheet, it doesn't work.

The entire Worksheet_Change event doesn't even trigger. In other words,
nothing happens at all.

Even when I set a step break to occur, it bypasses it. Don't know what to
do. Is there something that I need to do or "turn off" first (ie, rules,
etc.)??

Thanks.

That seemed to do the trick (application.enableevents=true).

Thanks for the assistance.
--
Craig


"Rowan" wrote:

Hi Craig

What have you done to test it? Bear in mind that this will not work on
data that is already on the sheet but rather any new value entered.

If you are sure you have pasted the code into the sheets code module,
enter DR in cell A1 and then -5000 in cell B1. If that doesn't work then
type
application.enableevents = true
in the VBE immediate window and hit enter.
Then try entering another -ve value in B1.

Regards
Rowan

Craig wrote:
Rowan:

That looks like exactly what I'm looking for, except one problem: When I
cut and paste it into my worksheet, it doesn't work.

The entire Worksheet_Change event doesn't even trigger. In other words,
nothing happens at all.

Even when I set a step break to occur, it bypasses it. Don't know what to
do. Is there something that I need to do or "turn off" first (ie, rules,
etc.)??

Thanks.

You're welcome

Craig wrote:
That seemed to do the trick (application.enableevents=true).

Thanks for the assistance.