The problem is that, in your macro, you're changing the value of the cell... so
that fires off the change event AGAIN.
What I would do is, in the first line of the macro, add:
Application.EnableEvents = False
then, before the End Sub, add:
Application.EnableEvents = True
It will then only run once.
Paul
________________________________
From: mridu paban boruah <mridujun...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Tuesday, June 16, 2009 10:02:55 AM
Subject: $$Excel-Macros$$ Event for cell data entry
Hi All,
I want a few cells to be in a definite number format.So while a user enters
data in those cells the code should check and throw up a msg if format is not
matching.
I hav used the below code with an event.
But the problem is when the code Range("B25").Value = "" is encountered the
loop starts from the beginning and runs indefinitely.
Please help!!! Is the event used is correct.
regards,
mridu
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$25" Then
If Not IsNumeric(Target) Then
MsgBox "Entry should be numeric for Total Cost Of Activity"
Range("B25").Value = ""
Range("B25").Activate
End If
Else
If Target.Address = "$H$27" Then
If Not IsNumeric(Target) Then
MsgBox "Entry should be in Percentage"
Range("H27").Value = ""
Range("H27").Activate
End If
Else
If Target.Address = "$B$16" Then
If IsDate(Target) = False Then
'MsgBox Range("B16").Value
MsgBox "Start Date should be in mm/dd/yyyy format and not exceed
End Date"
Range("$B$16").Select
Else
START_DATE = CDate(Range("B16").Value)
END_DATE = CDate(Range("H16").Value)
If START_DATE > END_DATE Then
MsgBox "Start Date Should not exceed End Date"
Range("B16").Value = ""
Exit Sub
End If
End If
End If
End If
End If
End Sub
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
