Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 = 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 On Wed, 14 Nov 2007 01:37:16 -0800, duckie wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing sheet reference to cell reference | Excel Worksheet Functions | |||
changing a cell to changing the link | Excel Worksheet Functions | |||
Changing Sheet Colour/Color | Excel Discussion (Misc queries) | |||
Protect from changing sheet name | Excel Discussion (Misc queries) | |||
what is the formula for changing the same cell on different sheet. | Excel Worksheet Functions |