Validation is not working

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to get a MsgBox to pop up if the value entered into a cell is
equal to or greater than the value in another cell, but no MsgBox ever comes
up. The code is in the sheet I'm working on. Help!

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address = "$H$3" Then
If Target.Value => "$B$3" Then
MsgBox "That Entry is NOT ALLOWED!"
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub
 
it works here, did you try pasting your code on the sheet code module of a clean
workbook?
 
Hi David,

Following seems to work :

Set Target = Range("$H$3")
If Target.Value >= Range("$B$3").Value Then
MsgBox "That Entry is NOT ALLOWED!"
Target.ClearContents
Target.Select
End If

HTH
Cheers
Carim
 
i probably should ask, no offense, but do you have the code on the sheet module
of the sheet you're entering your values into, and not it a standard code
module?
 
I closed and saved the workbook, reopened it and it worked the first time. I
made another change and nothing. Made several more changes, and nothing.
 
try this in place of your code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$H$3" Then
Set rng = Range("B3")
If Target.Value >= rng Then
MsgBox "That Entry is NOT ALLOWED!"
End If

End If
End Sub
 
Geez....I'm just getting nothing. I have the number 20 in B3 and try a bunch
of numbers in H3...and just nothing. VERY WEIRD! I even put the code on a
clean worksheet and still nothing. Here is the full code again I'm using:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
Set Target = Range("$H$3")
If Target.Value >= Range("$B$3").Value Then
MsgBox "That Entry is NOT ALLOWED!"
Target.ClearContents
Target.Select
End If
ws_exit:
Application.EnableEvents = True
End Sub
 
Thank you very much. That works as well!

Gary Keramidas said:
try this in place of your code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$H$3" Then
Set rng = Range("B3")
If Target.Value >= rng Then
MsgBox "That Entry is NOT ALLOWED!"
End If

End If
End Sub
Click to expand...
 
Searching found this close. Want to require entry into each cell in row not
allow blanks. Like the msgbox idea. Does not sound so demanding but gets the
job done.
will play with this and hopeing.
Thanks
 
the change event only fires when someone edits a cell. It seems unlikely
someone will edit a cell and leave it blank.

In the code I sent you I did put a cursory check to identify if there are
any blanks in column L before the current entry.
 
Yes what you sent is working fine.
This is an after thought. What I inherited from the last person was missing
data in the rows. Some entries have had to be scraped for this lack of data.
My valadation idea was to require a user to not leave any blanks in row.
Tried to do this with excel valadation on sheet no avail. Seems the more I
work on this the more I find that is needed. Most of data entry is done by
trainees at the centre. Most are having a hard time readjusting. My drive is
to make it a little easier for them.
Hope you can understand my passion for this. Also can excuse my lack of
experience in doing this. Not as Not as mean But still a Semper Fi
Added this as the help is great.
Thanks
 
Data validation only reacts to what a user enters. It can't force the user
to make an entry.

You would have to figure out the minimum everything that the user must do,
then figure out when to check to see if they did it.

If I I made an entry in one cell, then saved and closed the workbook.

If I made an entry in one cell and walked away. The next person starts
entering data and that triggers an event that locks up the sheet because of
the first persons action (no way to say an entry in a cell is incorrect
until some subsequent events makes it appear so).

So you see the complexity that could be involved. That is why many people
put up userforms to get data and populate sheets and don't give the data
entry people access to the sheet. Others just have people instruct/train
the people entering the data and manually check them after.

--
regards,
Tom Ogilvy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Target.Address Being One Of The Cells Of A Named Range 2
Macro converts time entered in column E and F from Indian time to CST( without daylight saving) 1
Trying to run excel vba code when cell value changes from empty to entered value. 1
urgent!!!!!! 3
COMBINE TWO MACROS INTO ONE 10
CORRECTION NEEDED 2
CAN ANYONE PLEASE SOLVE MY PROBLEM 1
MACRO IS NOT COMPLETE 2

Back
Top