VB Change Function, worked but cursor keep spinning loading

[bhayu]

Dear Experts,
I wrote these syntax below, I found them worked, but my cursor keep spinning loading, never ended to end sub.
At the end, I had to forcefully do 'crtl+alt+del' to close excel, otherwise cursor keep spinning loading endlessly.

Anyone has any idea why it does so? I’m very gratefull for any help.
Thank you very much

My syntax in Sheet1 :

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
TryOut
End If
End Sub

My syntax in Module :

Sub TryOut ()
with Sheets("sheet1").Range("A1")
answer =.Value
If Application.IsText(answer) Then
MsgBox "Fill out with Number"
.ClearContents

ElseIf answer > 5 Or answer < 3 Then
MsgBox "Fill out number that >3 or <5"
.ClearContents

Else
MsgBox "That’s Correct"
End If
End with
End Sub

[gmr4evr1]

It's the .ClearContents lines. They are causing excel to just loop through both sets of codes without stopping because it never gets to the EndIf lines.

[bhayu]

Dear Expert (gmr4evr1),

Thank you very much for your reply.

How to ClearContent if the Sheets("sheet1").Range("A1") contain wrong answer?

Thank you very much.

[gmr4evr1]

I've been trying to figure out a way to do that but haven't been able to yet. The problem is that when it clears the cell, it's considered a worksheet change so your Worksheet_Change code is triggered again and everything starts all over again.

[nimrod]

Momentarily turn of the "Events" trigger i.e. Change is an event.

Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("sheet1").Range("A1")
Application.EnableEvents = False
.ClearContents
Application.EnableEvents = True
End With
End Sub

[nimrod]

BUILT IN TOOL FOR VALIDATION:

Do you know that excel has built in feature for data validation ... On "Data Tab" check out "Data Validation".
The "Data Validation" will clear the cell and give messages ... AND work even in WB's that do not have macros enabled

[gmr4evr1]

Thanks for the assist nimrod. I wasn't sure if it would everything would work correctly if EnableEvents was places in the Worksheet_Change event, but I placed it in the TryOut sub and it worked.
Bhayu,
Here is the code with the EnableEvents added. See if it works for you..

Please Login or Register  to view this content.

[nimrod]

Hi gmr4evr1:
Glad that worked out

Bhayu should also check out excels built in validation feature as it does everything he's attempting to achieve without having to have a macro.

Cheers

[gmr4evr1]

I should probably check into it as well as I don't use it hardly at all.

[nimrod]

BUILT IN DATA VALIDATION FEATURE:

The built in data validation is quite robust . It allows you to form drop down lists, restrict values , clear fields for incorrect entries and provides 3 levels of messages : information, warning or error.

Very easy to use AND with work even when the user does not have macro's enabled.

[bhayu]

Dear Experts (gmr4evr1 and nimrod),

Thank you very much for the reply and direction. May this thread also be an answer and direction for others.

Actually I have known that excels built in validation feature, but I was still curious how to built it via Macro.

Thank you very much for the assistance

Best regards.

[gmr4evr1]

You're welcome.