changing tabs from a cell from a difference sheet

D

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
 
B

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)
 
J

Joel

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"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top