changing tabs from a cell from a difference sheet

[duckie]

i have try the following but it won't let me change the tab on sheet
2 where i put the code in
i want cell A1 on sheet 1 to change tab on sheet 2 the only way it
will change is if i type something in A! on sheet 2


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 = Sheets("Sheet1").Range(sNAMECELL).Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub



can someone please help me

 

[Bob Phillips]

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(Target, Me.Range(sNAMECELL)) Is Nothing Then
sSheetName = Target.Value
If Not sSheetName = "" Then
On Error Resume Next
Me.Name = sSheetName
On Error GoTo 0
If Not sSheetName = Me.Name Then _
MsgBox sERROR & sNAMECELL
End If
End If
End With
End Sub

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Your problem is with the intersect statement

If Not Intersect(.Cells, Range(sNAMECELL)) Is Nothing Then

You have a double negative. this stement is equivalent to
If Intersect(.Cells, Range(sNAMECELL)) Then

..Cells is the location where you changed the data and sNamecell is defined
as "A1"