Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sNAMECELL As String = "A1"
Const sERROR As String = "Invalid worksheet name in cell "
Dim sSheetName As String
With Target
If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then
sSheetName = Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Sheets(2).Name = sSheetName
On Error GoTo 0
If Not sSheetName = Sheets(2).Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub


Gord

On Wed, 14 Nov 2007 01:37:16 -0800, duckie wrote:

thanks for the help
it will only let me change the tab on sheet 1 where i put the code in
i want cell A1 on sheet 1 to change tab on sheet 2