Rename Sheets but.....
 

I am writing code to rename sheets with the text entered in cells B1:P1. I am
trying to handle 2 cases. First where the user types in the text one cell at
a time and second where the user pastes in several cells at a time. The
pasting part seems to work but when I enter data one cell at a time and hit
"Enter" it works but if I hit "TAB" to move to the next cell it fails becuase
the selection is now in the next cell...

Here is my code:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrorHandler
If Intersect(Range("B1:P1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Row = 1 Then
For Each Target In Selection
If SheetExists(Target.Value) Then
MsgBox "Sheet Name " & Target.Value & " already used!",
vbCritical, "Duplicate Sheet Name Error"
Application.EnableEvents = False
Application.Undo
Else
Select Case Target.Column
Case 2
Sheet2.Name = Target.Value
Case 3
Sheet3.Name = Target.Value

Rename Sheets but.....
 

Target is passed in as the range of cells that triggered the event. Best to
not try to change it as a matter of practice (it is byval, so it does no
damage). But using it as the cells to loop through avoids your problem.
Rarely is is appropriate to use selection in sheet event unless the code is
managing the selection (IMO).

If you Undo on a multicell paste - is that what you want to do? I would
guess not.

Private Sub Worksheet_Change(ByVal Target As Range)
dim cell as Range
On Error GoTo ErrorHandler
If Intersect(Range("B1:P1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Target.Row = 1 Then
For Each Cell in Target
If SheetExists(cell.Value) Then
MsgBox "Sheet Name " & cell.Value & " already used!",
vbCritical, "Duplicate Sheet Name Error"
Application.EnableEvents = False
'Application.Undo
' cell.clearcontents perhaps
Else
Select Case cell.Column
Case 2
Sheet2.Name = cell.Value
Case 3
Sheet3.Name = cell.Value

Rename Sheets but.....
 

It looks like this line is what's causing your problems

For Each Target In Selection

To avoid confusion, I suggest declaring another range variable and construct
your code like this:

Dim c as Range
For Each c In Target.Range
If SheetExists(c.Value) Then
MsgBox "Sheet Name " & c.Value & " already used!",
vbCritical, "Duplicate Sheet Name Error"
Application.EnableEvents = False
Application.Undo
Else
Select Case c.Column
Case 2
Sheet2.Name = c.Value
Case 3
Sheet3.Name = c.Value