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...

 

[omairhe]

Anyone??

 

[Norie]

That example file has no code in it.

And it took me about 5 minutes before I was able to download it.

 

[omairhe]

That example file has no code in it.

And it took me about 5 minutes before I was able to download it.

Hi...You can try it now. Also you have to enable macros for this ... then right-click on "sheet1" and select "View Code". its 20kb file ...
the problem is that my Vba and Formula in sheet1 don't work together very well...
Help will be appreciated.
Thank You.

 

[Norie]

Could you please tell us what the problem is?

PS I pretty much have macros enabled all the time, and I've downloaded the file, looked at the code but I still don't know what the problem is.

PPS I've just tried stepping through the code and it just seems to be wrong.

The change event appears to be calling the select change event, which then calls the change event again.

 

[omairhe]

Could you please tell us what the problem is?

PS I pretty much have macros enabled all the time, and I've downloaded the file, looked at the code but I still don't know what the problem is.

There is a Column "D" which has formula = C - E
and then there is Column E that has formula = SUM(G#:K#)

Now my macro states "If Target.Value > Range("D" & Target.Row).Value Then"
but even if the Target.Value is less than Range it doesn't process it that way. just try to enter values inside the range ="match".

try typing values less than D in Target.Row inside range "match"

it works fine when the formula (in Column "D" = C - E) is Not used but only Value is used and no formula...
any clue Sir

 

[Jiurasic]

Try such:

If Target.Value > Range("D" +Trim(Str(Target.Row))).Value Then

 

[omairhe]

Try such:

If Target.Value > Range("D" +Trim(Str(Target.Row))).Value Then

still the same result

 

[xenou]

Post your entire code and explain the purpose.

• I was under the impression that (in the subroutine) your value is subtracted from D before you compare them. It might be something which how Excel handles the order of calculations.
  
• you may want "less then or equal to" rather than "less than"
  
• I don't understand why you make the cell value equal to A2
  
• having a change event and a selection_change event is confusing and both procedures will run when a change occurs.

 

[omairhe]

Post your entire code and explain the purpose.

• I was under the impression that (in the subroutine) your value is subtracted from D before you compare them. It might be something which how Excel handles the order of calculations.
  
• you may want "less then or equal to" rather than "less than"
  
• I don't understand why you make the cell value equal to A2
  
• having a change event and a selection_change event is confusing and both procedures will run when a change occurs.

*Yes perhaps the calculations order might be the issue but not sure...
*can u make a suggestion with less than or equal to code line as i am still very new with Vba.
*A2 serves the purpose for when the value is already written inside range.match eg. (21) in Cell G3 of my sheet and when its been re-written which exceeds column.target D (Type 200 in G3) then the old value (21) may re-appear rather the new exceeding value (200).
*oki i agree this seems confusing therefore i am sending u new code that is short but still the problem persists. please kindly delete entire code and paste my new code

CODE:

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

Even still if the value is Less than "D" the MsgBox "Not Enough Fund" will appear which is wrong statement for this function. and Transaction Made should be appear in MsgBox. but it don't...

Thanks for your kind interest.