ExcelBanter - changing tab from another sheet cell

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - changing tab from another sheet cell (https://www.excelbanter.com/excel-worksheet-functions/165824-changing-tab-another-sheet-cell.html)

duckie

changing tab from another sheet cell

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

changing tab from another sheet cell

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"duckie" wrote in message
oups.com...
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

duckie

changing tab from another sheet cell

On Nov 13, 9:29 pm, "Max" wrote:
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
---"duckie" wrote in message

oups.com...

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- Hide quoted text -

- Show quoted text -

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

Max

changing tab from another sheet cell

"duckie" wrote
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.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Gord Dibben

changing tab from another sheet cell

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

On Tue, 13 Nov 2007 01:20:13 -0800, duckie wrote:

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

duckie

changing tab from another sheet cell

On Nov 14, 11:45 am, Gord Dibben <gorddibbATshawDOTca wrote:
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

On Tue, 13 Nov 2007 01:20:13 wrote:
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- Hide quoted text -

- 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

changing tab from another sheet cell

All times are GMT +1. The time now is 04:20 PM.