Macro for Tab Key

  • Thread starter Thread starter desperate in MS
  • Start date Start date
D

desperate in MS

I have the following macros to insert 2 rows when data is entered in column
"A".

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 10 Or Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Target.Offset(1).Resize(2).EntireRow.Insert
Target.Offset(2).Select
Application.EnableEvents = True
End Sub

Works like a charm...Say I enter 001 in column "A1", it inserts two rows
(A2, A3) but.... I need to be able to "tab" Key over from column "A" to
column "b" and so on. Right now if I tab after the two rows have been
itserted it just keeps adding rows unless I manually place my cursor in the
next column. This is something I could probably live with but would also like
to know if there is any other options????
 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row < 10 Or Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
Set t = Target
Target.Offset(1).Resize(2).EntireRow.Insert
Target.Offset(2).Select
t.Offset(0,1).Select
Application.EnableEvents = True
End Sub
 
Thanks...that worked great...time for me to turn the spreadsheet over for
use....everyone here was so helpful....thanks to you Gary's student...and the
rest
 
The example you gave with entering a value in A1 is misleading, since the
code excludes rows 1 through 10 from causing the change event to execute the
code.
However, I could not get it to duplicate the problem. When I tab after the
rows are inserted it simply moves to the adjacent cell per specifications.
 
Back
Top