SelectionChange - What am I doing wrong?

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

Guest

Please help. My code is working almost exactly as I want it to, with one exception.

Scenario:

Two Columns A and B
The user makes a change in Column A (example: User changes value of Cell A9 to 5). If the value of the adjacent cell (B5) is greater than the current cell, a msg box appears. Easy, simple, right?

The problem:

My code, thanks to Rob van Gelder and Tom Ogilvy, is working fine. But here is the problem. When the user changes the value of A9, nothing happens. The user will usually enter a new value into the cell and then hit Enter or move to the cell to the right.
If the user hits Enter, we move to the cell below, and my code is not generating a message, as we are now on a different cell (the comparison of the two cells fails).
If the user moves to the right, then he is in column B, and my code is only checking for new entries in Column A.
How can I fix this? Should I not be using the
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
as my main method of looking for a change?

Or should I simply check both columns A and Columns B? What I need is something that does a check as soon as the user moves to a different cell. Thanks. This is giving me a headache.

Doug
 
Target indicates the cell that triggered the event - a you should be using
the change event, not the selectionchange

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

if Target.Count >1 then exit sub
if Target.Column = 1 then
if Target.Value < Target.offset(0,1).Value then
msgbox "Some Message"
end if
end if
End sub

--
Regards,
Tom Ogilvy


Doug said:
Please help. My code is working almost exactly as I want it to, with one exception.

Scenario:

Two Columns A and B
The user makes a change in Column A (example: User changes value of Cell
Click to expand...
A9 to 5). If the value of the adjacent cell (B5) is greater than the current
cell, a msg box appears. Easy, simple, right?
The problem:

My code, thanks to Rob van Gelder and Tom Ogilvy, is working fine. But
Click to expand...
here is the problem. When the user changes the value of A9, nothing happens.
The user will usually enter a new value into the cell and then hit Enter or
move to the cell to the right.
If the user hits Enter, we move to the cell below, and my code is not
Click to expand...
generating a message, as we are now on a different cell (the comparison of
the two cells fails).
If the user moves to the right, then he is in column B, and my code is
Click to expand...
only checking for new entries in Column A.
How can I fix this? Should I not be using the
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
as my main method of looking for a change?

Or should I simply check both columns A and Columns B? What I need is
Click to expand...
something that does a check as soon as the user moves to a different cell.
Thanks. This is giving me a headache.
 
The only thing I can think of is the default settings. Perhaps in tools,
options, edit, the user needs to change the move after enter selection
 
Tom,

Thank you again. You are a lifesaver of the first degree. I wish you a safe and prosperous 2004.

Doug
 
Back
Top