Validation is not working

[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

 

[Gary Keramidas]

If Target.Value >= Range("$B$3").Value Then

 

[Guest]

Still no message box

If Target.Value >= Range("$B$3").Value Then

 

[Gary Keramidas]

it works here, did you try pasting your code on the sheet code module of a clean
workbook?

 

[Carim]

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

 

[Gary Keramidas]

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?

 

[Guest]

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.

 

[Carim]

David,

For your new tests, make sure

Application.EnableEvents = True

is on ...

HTH
Carim

 

[Gary Keramidas]

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

 

[Guest]

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

 

[Guest]

I just got rid of both application event code lines and working fine now.
Thanks SO much!!

 

[Guest]

Thank you very much. That works as well!

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

 

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Guest]

Thank You for the knowledge about input Will work around it . Now I know.
Thanks Again