Data Validation Via VBA Code

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
That example file has no code in it.:eek:

And it took me about 5 minutes before I was able to download it.:)
 
Upvote 0
Norie said:
That example file has no code in it.:eek:

And it took me about 5 minutes before I was able to download it.:)
Click to expand...

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.
 
Upvote 0
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.:)
 
Last edited:
Upvote 0
Norie said:
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.:)
Click to expand...

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 :)
 
Last edited:
Upvote 0
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.
 
Upvote 0
xenou said:
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.
Click to expand...

*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:
PHP: 
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.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA to protect/unprotect sheet...
Replies
6
Views
446
jkpieterse
jkpieterse
K
  • Locked
  • Question Question
Vba code for User history log which is giving me a run-time error "13" mismatch
Replies
2
Views
186
Fluff
Fluff
D
  • Question Question
Modify a worksheet without registering it as a change
Replies
1
Views
181
Kaper
Kaper
S
  • Question Question
Can you Automatically Update Formulas and Data Validation Between 2 cells in Excel Using VBA
Replies
1
Views
98
Fluff
Fluff
M
  • Solved
Data Validation question
Replies
4
Views
397
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,225,369
Messages
6,184,559
Members
453,243
Latest member
Jemini Jimi

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top