changing tab from another sheet cell

[duckie]

i have tried this but i need the cell to come from sheet 1 not 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 = 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

 

[Max]

Think the sheet code that you quoted will simply rename the sheet tab
according to what's input into cell A1 or returned via a formula in cell A1.

To install the code into any sheet, just copy the code, then do a
right-click on the sheet tab > Choose "View Code". Then paste the code into
the code window. Press Alt+Q to get back to Excel. Test it out by inputting
into A1, say: ItWorks, press Enter. The sheet tab will change to the name:
ItWorks.

The code will prompt if the input in A1 contains either invalid characters
for sheetnames or if the sheetname input already exists.

 

[duckie]

Think the sheet code that you quoted will simply rename the sheet tab
according to what's input into cell A1 or returned via a formula in cell A1.

To install the code into any sheet, just copy the code, then do a
right-click on the sheet tab > Choose "View Code". Then paste the code into
the code window. Press Alt+Q to get back to Excel. Test it out by inputting
into A1, say: ItWorks, press Enter. The sheet tab will change to the name:
ItWorks.

The code will prompt if the input in A1 contains either invalid characters
for sheetnames or if the sheetname input already exists.
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik







- Show quoted text -

i have done that but i need a cell in sheet 1 to change the tab on
sheet 2

 

[Max]

i have done that but i need a cell in sheet 1 to change the tab on
sheet 2

Try posting in .programming if nobody drops by here.

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP

 

[duckie]

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

Gord Dibben MS Excel MVP







- Show quoted text -

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

 

[Gord Dibben]

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