Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW

Derek,

Use data validation, with a custom formula: For cell A1, use

=AND(ISNUMBER(A1),A1=0,A1<=100,INT(4*A1)=4*A1)

Change the error message to whatever you want...

HTH,
Bernie
MS Excel MVP


"Derek Wittman" wrote in message
...
Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsEmpty(Target.Value) Then
If Target.Value < 0 Then
MsgBox "No negative numbers. Please make the correction."
Target.Select
SendKeys "{F2}"
ElseIf Int(Target) < Target Then
If Int(Target * 4) < Target * 4 Then
MsgBox "Your cell is not evenly divisible by 0.25." & _
" Please make the correction."
Target.Select
SendKeys "{F2}"
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Derek Wittman" wrote:

Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW

Thanks, Tom. Unfortunately, I'm getting what appears to be nothing
happening. No idea why, as I have macros enabled.

Thanks for trying!
DW

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsEmpty(Target.Value) Then
If Target.Value < 0 Then
MsgBox "No negative numbers. Please make the correction."
Target.Select
SendKeys "{F2}"
ElseIf Int(Target) < Target Then
If Int(Target * 4) < Target * 4 Then
MsgBox "Your cell is not evenly divisible by 0.25." & _
" Please make the correction."
Target.Select
SendKeys "{F2}"
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Derek Wittman" wrote:

Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("A1:A10") is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Value 0 and Target.Value < 100 Then Exit Sub
If Target.Value Mod .25 = 0 Then Exit Sub
MsgBox "Your cell does not contain a number. Please make" & _
"the correction."
End Sub

Try that for starters

Charles Chickering
Derek Wittman wrote:
Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW

where did you put the code?

right click on the sheet module and select view code.

then put it in that module.

Make sure that events are enabled.

In a general module, put in this code and run it

Sub AAA()
Application.EnableEvents = True
End Sub

here is a revised version of the code that guards against alphanumeric
entries and restricts the cells where it operates:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2:C31"), Target) Is Nothing Then
If Not IsEmpty(Target.Value) Then
If IsNumeric(Target) Then
If Target.Value < 0 Then
MsgBox "No negative numbers. Please make the correction."
Target.Select
SendKeys "{F2}"
ElseIf Int(Target) < Target Then
If Int(Target * 4) < Target * 4 Then
MsgBox "Your cell is not evenly divisible by 0.25." & _
" Please make the correction."
Target.Select
SendKeys "{F2}"
End If
End If
Else
MsgBox "All entries must be numeric"
Target.Select
SendKeys "{F2}"
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Derek Wittman" wrote:

Thanks, Tom. Unfortunately, I'm getting what appears to be nothing
happening. No idea why, as I have macros enabled.

Thanks for trying!
DW

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsEmpty(Target.Value) Then
If Target.Value < 0 Then
MsgBox "No negative numbers. Please make the correction."
Target.Select
SendKeys "{F2}"
ElseIf Int(Target) < Target Then
If Int(Target * 4) < Target * 4 Then
MsgBox "Your cell is not evenly divisible by 0.25." & _
" Please make the correction."
Target.Select
SendKeys "{F2}"
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Derek Wittman" wrote:

Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW

the mod operator rounds the values on each side, so this will result in a
divide by zero error. At least it does for me.

--
Regards,
Tom Ogilvy




"Die_Another_Day" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target,Range("A1:A10") is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Value 0 and Target.Value < 100 Then Exit Sub
If Target.Value Mod .25 = 0 Then Exit Sub
MsgBox "Your cell does not contain a number. Please make" & _
"the correction."
End Sub

Try that for starters

Charles Chickering
Derek Wittman wrote:
Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW

Tom, I had the code in Module1. I moved it to the Sheet where my data is
maintained - thanks for that suggestion. (I'm more in tune with VBA in
Access - this is my first attempt at adding code to Excel)

I put the revised code (for the alphanumeric contents) and it works well.
The only problem I have is that once I put an unacceptable value into the
cell, if I hit enter to close the msgbox, then hit ESC, I can actually keep
the value in the cell. I'll try to modify so the cell is made null when the
user closes the msgbox.

Thanks again! This is fantastic!
DW

"Tom Ogilvy" wrote:

where did you put the code?

right click on the sheet module and select view code.

then put it in that module.

Make sure that events are enabled.

In a general module, put in this code and run it

Sub AAA()
Application.EnableEvents = True
End Sub

here is a revised version of the code that guards against alphanumeric
entries and restricts the cells where it operates:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("B2:C31"), Target) Is Nothing Then
If Not IsEmpty(Target.Value) Then
If IsNumeric(Target) Then
If Target.Value < 0 Then
MsgBox "No negative numbers. Please make the correction."
Target.Select
SendKeys "{F2}"
ElseIf Int(Target) < Target Then
If Int(Target * 4) < Target * 4 Then
MsgBox "Your cell is not evenly divisible by 0.25." & _
" Please make the correction."
Target.Select
SendKeys "{F2}"
End If
End If
Else
MsgBox "All entries must be numeric"
Target.Select
SendKeys "{F2}"
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Derek Wittman" wrote:

Thanks, Tom. Unfortunately, I'm getting what appears to be nothing
happening. No idea why, as I have macros enabled.

Thanks for trying!
DW

"Tom Ogilvy" wrote:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not IsEmpty(Target.Value) Then
If Target.Value < 0 Then
MsgBox "No negative numbers. Please make the correction."
Target.Select
SendKeys "{F2}"
ElseIf Int(Target) < Target Then
If Int(Target * 4) < Target * 4 Then
MsgBox "Your cell is not evenly divisible by 0.25." & _
" Please make the correction."
Target.Select
SendKeys "{F2}"
End If
End If
End If
End Sub

--
Regards,
Tom Ogilvy


"Derek Wittman" wrote:

Good afternoon,
I've got a range of cells that I'd like to (whenever one changes) run the
VBA code against. I'd like the code to check that the changed cell has a
number value (either whole or in increments of 0.25). The cell may be null -
that's an acceptible value also. If the cell is not null, nor a positive
increment of 0.25, I'd like to automate a msgbox. Then, I'd like the cell to
be highlighted (as if it's the activecell with an F2 to edit it) to give the
user the opportunity to change the value of the cell. If it's still wrong,
I'd like to have it rerun the code.

Here's what I have so far, and it does NOTHING (that I can see):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Target.Value Then
If Target.Value 0 Then
If Target.Value < 100 Then
End If
End If
Else: MsgBox "Your cell does not contain a number. Please make the
correction."
End If
End Sub

What a) am I doing wrong, and b) do I need to do to complete my code?

All help will be greatly appreciated. Thank you!
DW