Data Validation Via VBA Code

[omairhe]

Hi can someone guide me through this and why it not work like it should...
im having trouble with

If Target.Value > Range("D" & Target.Row).Value Then

Please have a look at my example Sheet...
http://d01.megashares.com/dl/cb60c6e/Data Validation via VBA Code.xlsm

Any Help will be Appreciated...

 

[mikerickson]

Rather than storing the value in A2, you could use Application.Undo and avoid the Selection Change event. The back and forth between the two events triggering each other is too congested.

 

[omairhe]

Rather than storing the value in A2, you could use Application.Undo and avoid the Selection Change event. The back and forth between the two events triggering each other is too congested.

thank you but thats not the point here.. the Code even when shortened, the problem persists... any work around will be appreciated .. my new shortened code in my previous post

Private Sub Worksheet_Change(ByVal Target As Range) 
If Intersect(Target, Range("match")) Is Nothing Then Exit Sub 
If Target.Value > Range("D" & Target.Row).Value Then 
    MsgBox "Not Enough Fund" 
    Exit Sub 
Else 
MsgBox "Transaction Made" 
End If 
End Sub

 

[mikerickson]

1) Please do not use PHP tags when you post. It fouls up the copy paste from message board to Excel. Use the code tag (#) instead.

2) The posted code seemed to work fine for me. Entering a large number brought up the "Not engough Fund" box, a small number "Transaction made".

If you are having problems, did your Application.EnableEvents get "stuck" on False during a debugging error/reset?

 

[omairhe]

2) The posted code seemed to work fine for me. Entering a large number brought up the "Not engough Fund" box, a small number "Transaction made".

it only partly works....
Suppose Target.Row Value in Column D = 100
you said you typed value less than D (for instance =5) "Transaction made"
but have you typed value = 90 which is again less than D's Value (100) but i bet it will show "Not enough Fund"

 

[mikerickson]

I'll have to try that when I get back to the file at home.

 

[mikerickson]

I looked at it again, and found the source of the problem.

C4 has 200
G4 has 100 and H4:K4 are empty

D4 has the formula =C4-E4 (shows 100)
E4 has the formula =SUM(G4:K4) (shows 100)


Entering 90 into H4, triggers the calculation so that the value in E4 is 190 and the value in D4 is 10.

Then the Change event (triggered by the entry into H4, not the resulting calculations) runs, and since D4 (10) is not greater than H4 (90), the "Not enough fund" message shows.

The problem is that with the testing cell (D4) being dependent on the value in Target, a circular reference has been set up.

 

[mikerickson]

You might consider changing the Validation test to "If col D < 0 then Bad". The Validation could be done by either the Change envent or by built-in Validation. In either case, this test will be made AFTER the formulas have calculated the newly entered value into their results.

 

[omairhe]

You might consider changing the Validation test to "If col D < 0 then Bad". The Validation could be done by either the Change envent or by built-in Validation. In either case, this test will be made AFTER the formulas have calculated the newly entered value into their results.

thats wonderful Mike...
i guess you figure that one out.. i am looking to do with Vba and not data validation tool in excel. you know im not so advance in Vba. could u be kind enough to add "If col D < 0 then Bad" in my code if not much trouble..
Thank U.