Test if data entered in two columns of same row.

  • Thread starter Thread starter rob nobel
  • Start date Start date
R

rob nobel

The following bit of code is my attempt to test if data has been entered in
2 columns, but when the next cell selected is not in the same row, ie. if
another cell is selected outside that row, by mouse or keyboard (enter or
arrows, etc), it doesn't work too well.
How can this be accomplished so that as soon as an amount is entered in
either of the two columns "H" or "I", that the data in the other column is
deleted (if there is any) & that the message appears after deleting the
other column's data.
I notice that fiddling around with trying to do this, that calculation time
is increased quite a bit, even for cells that aren't directly related to
this test. So I would like to have a procedure that strictly only tests
those two columns, if possible.
Please help.
Rob

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Me.Cells(Target.Row, "H") And Me.Cells(Target.Row, "I") <> "" Then
MsgBox "You cannot enter an amount for both credit and debit. Please correct
before proceeding."
ActiveCell.ClearContents
End If
 
try this on your worksheet module


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Select Case Target.Column
Case 8, 9 ' col h & i
If Sheets("sheet1").Cells(Target.Row, "h") <> "" An
Sheets("sheet1").Cells(Target.Row, "i") <> "" Then
MsgBox "You cannot enter an amount for both credit and debit.
_
& vbLf & "Please correct before proceeding."
vbExclamation
Target.ClearContents
End If
End Select

End Su
 
Hi mudraker and thankyou. The problem still persists, however, as your
procedure seems to be just a variation to what I had.

The problem......
When the data is entered in either col h or i when the other column already
has data in it, nothing happens until you RETURN to either of those cells
again. In other words, I need a message appear as soon as the data is
entered (after exiting either cell.....NOT when returning to either cell).
Rob
 
You wrote that you wanted the other cell cleared. But if it's cleared, then the
user doesn't have to correct it????

When you're inside a worksheet event and do something to it--change a value
(.clearcontents, too) or make a different selection, then that event fires.

So your code could be firing lots of times. Put a break point in your code and
then you can step through it as it fires, then fires again, and more (depending
on what your code does).

But you can tell excel to stop looking for events. Then do your stuff and then
tell it to start looking again.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Dim otherCell As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("H:I")) Is Nothing Then Exit Sub
If Application.CountA(Me.Cells(Target.Row, "H").Resize(1, 2)) < 2 Then
Exit Sub
End If

'If Target.Column = 9 Then
' Set otherCell = Me.Cells(Target.Row, 8)
'Else
' Set otherCell = Me.Cells(Target.Row, 9)
'End If
'or just:
Set otherCell = Me.Cells(Target.Row, 17 - Target.Column)

On Error GoTo errHandler:

Application.EnableEvents = False
otherCell.ClearContents
MsgBox "You cannot enter an amount for both credit and debit." & _
vbLf & "The value in " & otherCell.Address(0, 0) & _
" has been cleared!"
Target.Select '?? go back


errHandler:
Application.EnableEvents = True

End Sub

This is under the worksheet_change event--so it fires as soon as the user hits
enter/tabs off the cell.
 
Thanks Dave. Great help again!

"You wrote that you wanted the other cell cleared. But if it's cleared,
then the
user doesn't have to correct it????"

That's true but in the process of trying to do this, I got "lost".
What you have provided will be a great help as I'll be able to amend it to
do what I need. (I needed your helping hand again to get it going!)
PS. I like the bit informing that the value has been deleted.
Rob
 
Dave, just a coupla Q with this procedure.
1. I notice that after running this procedure that there is no facility to
undo any of the actions. I imagine this might be a huge task in programming
to impliment, but if it is a fairly "simple" process, could you include this
in the procedure, below?

2. If the user chooses to click out of the cell into a cell that causes
some other code to run, due to another Worksheet_SelectionChange event,
then that event takes place as soon as OK is clicked on this MsgBox.
I would like it that only this procedure is invoked and the selected cell's
procedure is ignored.
Possible?
Rob

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim otherCell As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("H:I")) Is Nothing Then Exit Sub
If Application.CountA(Me.Cells(Target.Row, "H").Resize(1, 2)) < 2 Then
Exit Sub
End If
Set otherCell = Me.Cells(Target.Row, 17 - Target.Column)
On Error GoTo errHandler:
Application.EnableEvents = False
otherCell.ClearContents
Target.Select
MsgBox "You cannot enter an amount for both credit and debit." & _
vbLf & "The value in " & otherCell.Address(0, 0) & _
" has been cleared!"
errHandler:
Application.EnableEvents = True
End Sub
 
You have to write your own undo functions.

John Walkenbach has an example at:
http://j-walk.com/ss/excel/tips/tip23.htm

Most non-trivial macros destroy the undo stack, though.

And the easy part <bg>:

I don't think you can stop the events from firing, but you can control what they
do.

I added a boolean variable (StopSelectionChange) where I kept track if it was ok
to continue:

Option Explicit
Dim StopSelectionChange

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim otherCell As Range

StopSelectionChange = False
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("H:I")) Is Nothing Then Exit Sub
If Application.CountA(Me.Cells(Target.Row, "H").Resize(1, 2)) < 2 Then
Exit Sub
End If

Set otherCell = Me.Cells(Target.Row, 17 - Target.Column)

On Error GoTo errHandler:
StopSelectionChange = True
Application.EnableEvents = False
otherCell.ClearContents
application.goto target
MsgBox "You cannot enter an amount for both credit and debit." & _
vbLf & "The value in " & otherCell.Address(0, 0) & _
" has been cleared!"
errHandler:
Application.EnableEvents = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If StopSelectionChange = True Then Exit Sub
'your original code here
MsgBox "hi from _selectionChange"
End Sub

And it worked ok in my simple testing.

Notice the change from:
target.select
to
application.goto target

If the user entered the 2nd value and then selected another sheet (no enter, no
tab), then the code would blow up (trying to select a cell on a worksheet that
wasn't active).

The errhandler would be invoked (skipping the error message).
 
Back
Top