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

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)



"duckie" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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"



duckie" wrote:

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