×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Worksheet Change Event, Enter vs. Tab vs. Cursor movement

Worksheet Change Event, Enter vs. Tab vs. Cursor movement

Worksheet Change Event, Enter vs. Tab vs. Cursor movement

(OP)
I checked the forum and this question has been asked but the answer was too vague for my understanding. The macro acts on different rows depending on weather the enter key is used or tab or cursor up, down , left, right. I want whatever method triggers the event to have the same effect as pressing enter. Code follows, comments will tell you what's supposed to happen. It works great as long as Enter key is used to trigger event. Feel free to throw any other improvement tips my way. I can always use more knowledge.

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
'
' This macro copies the row where an inspection date was entered in column F and
' inserts it in the next row with the next inspection date according to the inspection interval
'
Application.ScreenUpdating = False
Application.EnableEvents = True
'
Dim lastrow As Long
Dim RowNum As Long

' Find the last row of data in column A (the 1 in the formula)
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row

' Check if macro should be on or off
    If Range("F3").Value = "Off" Then Exit Sub
    
' Skip over code if cell that is changed is not in column F
    If Not Intersect(Target, Range("F6:F" & lastrow)) Is Nothing Then
    
' Do nothing if more than one cell is changed or content deleted
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

' Get the active row number
    RowNum = ActiveCell.Row

' Turn off events so macro doesn't go into endless loop
Application.EnableEvents = False

' Copy row of data that inspection date was entered into !!!!!!!! Copy only to column F !!!!!!
    Range("A" & RowNum - 1 & ":" & "F" & RowNum - 1).Copy
    
' Check if the cell in the "Indicate Violation column is not empty
        If Range("E" & RowNum - 1).Value <> "" Then
        
' If *** violation *** is indicated insert data into next 2 rows - *******************************
        Range("A" & RowNum & ":J" & RowNum + 1).Insert Shift:=xlDown

' Add 1 year to the date in column F, one row below the entered inspection date
        Range("F" & RowNum).Formula = "=DATE(YEAR(F" & RowNum - 1 & ") + 1" & ",MONTH(F" & RowNum - 1 & "),DAY(F" & RowNum - 1 & "))"
' Add 2 years to the date in column F, two rows below the entered inspection date
        Range("F" & RowNum + 1).Formula = "=DATE(YEAR(F" & RowNum - 1 & ") + 2" & ",MONTH(F" & RowNum - 1 & "),DAY(F" & RowNum - 1 & "))"

' Change the formula to a value
        Range("F" & RowNum & ":F" & RowNum + 1).Value = Range("F" & RowNum & ":F" & RowNum + 1).Value
        
' Change the color of the two new "Inspection Interval" cells to red, the values to one and blank the violation indicators
        Range("D" & RowNum & ":D" & RowNum + 1).Interior.Color = RGB(255, 0, 0)
        Range("D" & RowNum & ":D" & RowNum + 1).Value = 1
        Range("E" & RowNum & ":E" & RowNum + 1).Value = ""
        
' Move back to the originally selected cell
'    ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
        
        Else
    
' If *** no violation *** is indicated insert data into next row - ******************************
        Rows(RowNum & ":" & RowNum).Insert Shift:=xlDown
    
' Add the year in column F current row to the number in column D current row to get next inspection date
        Range("F" & RowNum).Formula = "=DATE(YEAR(F" & RowNum - 1 & ") + D" & RowNum - 1 & ",MONTH(F" & RowNum - 1 & "),DAY(F" & RowNum - 1 & "))"
' Changer the formula to a value
        Range("F" & RowNum).Value = Range("F" & RowNum).Value
    
' Move back to the originally selected cell
'    ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate
    
        End If
    End If
Application.CutCopyMode = False
    
' Turn events back on
Application.EnableEvents = True
Application.ScreenUpdating = True
    
End Sub 

Thanks, renigar

RE: Worksheet Change Event, Enter vs. Tab vs. Cursor movement

The 'change' event fires when the worksheet's contents that can be edited in the formula bar changes.
So you may need either Application.OnKey method or API function to handle key actions. They are linked to whole excel instance.
The more secure are native excel worksheet events, you may consider adding 'SelectionChange' event to your project.

combo

RE: Worksheet Change Event, Enter vs. Tab vs. Cursor movement

Quote:

It works great as long as Enter key is used to trigger event.

As combo pointed out the Enter key has nothing to do with the Worksheet Change event. The change has to do with characters changing in the Value of the Target range.

In your code you seem concerned with the ActiveCell, Activate, the originally selected cell. I would NEVER use ActiveCell or Activate in this event.

You seem to forget that the key element in this event processing is the Target range object. If you must "Move back to the originally selected cell" then...

CODE

Target.Select 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Worksheet Change Event, Enter vs. Tab vs. Cursor movement

BTW, your apparent issue, associated with "Enter vs. Tab vs. Cursor movement" hinged on where the ActiveCell is at the moment the event fires.

Here's the rub. When you hit Enter, the ActiveCell becomes the cell just below the Top-Left corner of the Target range. When you Tab, its the cell to the right of, unless its in a Structured Table where the Target cell is in a table with multiple columns and in the last column of the table, in which case the ActiveCell would be in the next row of the first column of the table.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Worksheet Change Event, Enter vs. Tab vs. Cursor movement

(OP)
Combo and Skip,
Let me research what you've given me and see what I can do.
Thanks for the tips,
renigar

RE: Worksheet Change Event, Enter vs. Tab vs. Cursor movement

(OP)
Skip,
Your statement about not using ActiveCell or Activate and focusing on the target range object clicked. It made all the difference. Now the code works no matter how the user leaves the cell. Thanks again for sharing your knowledge. I had to make a few additional small changes to the code once I figured it out. I am posting revised code in case it could help someone else understand.
renigar

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
'
' This macro copies the row where an inspection date was entered in column F and
' inserts it in the next row with the next inspection date according to the inspection interval
'
Application.ScreenUpdating = False
Application.EnableEvents = True
'
Dim lastrow As Long
Dim RowNum As Long

' Find the last row of data in column A (the 1 in the formula)
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row

' Check if macro should be on or off
    If Range("F3").Value = "Off" Then Exit Sub
    
' Skip over code if cell that is changed is not in column F
    If Not Intersect(Target, Range("F6:F" & lastrow)) Is Nothing Then
    
' Do nothing if more than one cell is changed or content deleted
        If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

' Get the active row number
    RowNum = Target.Row  ' Changed from RowNum = ActiveCell.Row made the difference 

' Turn off events so macro doesn't go into endless loop
Application.EnableEvents = False

' Copy row of data that inspection date was entered into !!!!!!!! Copy only to column F !!!!!!
'    Range("A" & RowNum - 1 & ":" & "F" & RowNum - 1).Copy
    Range("A" & RowNum & ":" & "F" & RowNum).Copy
    
' Check if the cell in the "Indicate Violation column is not empty
        If Range("E" & RowNum).Value <> "" Then
        
' If *** violation *** is indicated insert data into next 2 rows - *******************************
        Range("A" & RowNum + 1 & ":F" & RowNum + 2).Insert Shift:=xlDown

' Add 1 year to the date in column F, one row below the entered inspection date
        Range("F" & RowNum + 1).Formula = "=DATE(YEAR(F" & RowNum & ") + 1" & ",MONTH(F" & RowNum & "),DAY(F" & RowNum & "))"
' Add 2 years to the date in column F, two rows below the entered inspection date
        Range("F" & RowNum + 2).Formula = "=DATE(YEAR(F" & RowNum & ") + 2" & ",MONTH(F" & RowNum & "),DAY(F" & RowNum & "))"

' Change the formula to a value
        Range("F" & RowNum + 1 & ":F" & RowNum + 2).Value = Range("F" & RowNum + 1 & ":F" & RowNum + 2).Value
        
' Change the color of the two new "Inspection Interval" cells to red, the values to one and blank the violation indicators
        Range("D" & RowNum + 1 & ":D" & RowNum + 2).Interior.Color = RGB(255, 0, 0)
        Range("D" & RowNum + 1 & ":D" & RowNum + 2).Value = 1
        Range("E" & RowNum + 1 & ":E" & RowNum + 2).Value = ""
        
' Move back to the originally selected cell
        Target.Select
        
        Else
    
' If *** no violation *** is indicated insert data into next row - ******************************
        Rows(RowNum + 1 & ":" & RowNum + 1).Insert Shift:=xlDown
    
' Add the year in column F current row to the number in column D current row to get next inspection date
        Range("F" & RowNum + 1).Formula = "=DATE(YEAR(F" & RowNum & ") + D" & RowNum & ",MONTH(F" & RowNum & "),DAY(F" & RowNum & "))"
' Change the formula to a value
        Range("F" & RowNum + 1).Value = Range("F" & RowNum + 1).Value
    
' Move back to the originally selected cell
        Target.Select
    
        End If
    End If
    
Application.CutCopyMode = False
    
' Turn events back on
Application.EnableEvents = True
Application.ScreenUpdating = True
    
End Sub 

RE: Worksheet Change Event, Enter vs. Tab vs. Cursor movement

renigar,
To show the appreciation for help, click on Great Post! link in Skip's reply.


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close