Hi,

I'm using a change event to run a routine whenever a specific cell (B3)
changes. That cell uses Excel's data validation tool to restrict the number
of values that can be entered. The change event macro works fine whenever
the user enters a valid value. However, it also runs whenever the user
enters an invalid value and then clicks "Cancel" on the validation tool pop-
up error message. Is there any way to keep the macro from running if the
user attempts to cancel the data entry attempt? Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

I don't think you can stop it from running. You probably could check against
the criteria to see whether it is valid or not and only act when it is valid.


--
Regards,
Tom Ogilvy


"cholley via OfficeKB.com" wrote:

Hi,

I'm using a change event to run a routine whenever a specific cell (B3)
changes. That cell uses Excel's data validation tool to restrict the number
of values that can be entered. The change event macro works fine whenever
the user enters a valid value. However, it also runs whenever the user
enters an invalid value and then clicks "Cancel" on the validation tool pop-
up error message. Is there any way to keep the macro from running if the
user attempts to cancel the data entry attempt? Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

Hi there,

Check your cell in code against your validation criteria.

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"cholley via OfficeKB.com" <u19904@uwe wrote in message
news:5dfd172cc8f27@uwe...
Hi,

I'm using a change event to run a routine whenever a specific cell (B3)
changes. That cell uses Excel's data validation tool to restrict the
number
of values that can be entered. The change event macro works fine whenever
the user enters a valid value. However, it also runs whenever the user
enters an invalid value and then clicks "Cancel" on the validation tool
pop-
up error message. Is there any way to keep the macro from running if the
user attempts to cancel the data entry attempt? Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1

You could trap it

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "H1:H20"
Static prevValue

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Value < prevValue Then
'do your stuff
prevValue = .Value
End If
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"cholley via OfficeKB.com" <u19904@uwe wrote in message
news:5dfd172cc8f27@uwe...
Hi,

I'm using a change event to run a routine whenever a specific cell (B3)
changes. That cell uses Excel's data validation tool to restrict the
number
of values that can be entered. The change event macro works fine whenever
the user enters a valid value. However, it also runs whenever the user
enters an invalid value and then clicks "Cancel" on the validation tool
pop-
up error message. Is there any way to keep the macro from running if the
user attempts to cancel the data entry attempt? Thanks.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1