Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA rename sheet.
Hello everyone I have managed to get a VBA to change the name of a sheet for me which is now displayed below. The below is thanks to HTH Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheets(" Name = Range("K1").Value End Sub But the problem now is after it as changed the name from "Temp 11" to Range name in cell K1 the above formula now keeps looking for Sheet name "Temp 11" everytime I open the sheet. Could someone please help tell how to tell it to ignor if unable to find the sheet name "Temp 11". I am only new at this but I am a very persistant person and plan on learning as much as I can. Paul Maynard Moscow Russia. -- paulrm906 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA rename sheet.
probably should save as that name...
-- paul remove nospam for email addy! "paulrm906" wrote: Hello everyone I have managed to get a VBA to change the name of a sheet for me which is now displayed below. The below is thanks to HTH Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheets(" Name = Range("K1").Value End Sub But the problem now is after it as changed the name from "Temp 11" to Range name in cell K1 the above formula now keeps looking for Sheet name "Temp 11" everytime I open the sheet. Could someone please help tell how to tell it to ignor if unable to find the sheet name "Temp 11". I am only new at this but I am a very persistant person and plan on learning as much as I can. Paul Maynard Moscow Russia. -- paulrm906 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA rename sheet.
That code errors for me. What formula are you referring to?
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "paulrm906" wrote in message .. . Hello everyone I have managed to get a VBA to change the name of a sheet for me which is now displayed below. The below is thanks to HTH Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheets(" Name = Range("K1").Value End Sub But the problem now is after it as changed the name from "Temp 11" to Range name in cell K1 the above formula now keeps looking for Sheet name "Temp 11" everytime I open the sheet. Could someone please help tell how to tell it to ignor if unable to find the sheet name "Temp 11". I am only new at this but I am a very persistant person and plan on learning as much as I can. Paul Maynard Moscow Russia. -- paulrm906 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA rename sheet.
Are you trying to rename the same sheet that owns this code?
If yes: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Me.Name = Me.Range("K11").Value If Err.Number < 0 Then MsgBox "Rename failed" Err.Clear End If On Error GoTo 0 End Sub If wanted to rename a different sheet in the workbook, you could use the code name. That's the name you see in the project explorer. CodeName WorksheetName Sheet3 (WhatYouSeeOnTheTab) Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next Sheet3.Name = Me.Range("K11").Value If Err.Number < 0 Then MsgBox "Rename failed" Err.Clear End If On Error GoTo 0 End Sub I used the worksheet_change event. You were trying to rename the sheet each time you changed selection. I didn't understand that. paulrm906 wrote: Hello everyone I have managed to get a VBA to change the name of a sheet for me which is now displayed below. The below is thanks to HTH Private Sub Worksheet_SelectionChange(ByVal Target As Range) Worksheets(" Name = Range("K1").Value End Sub But the problem now is after it as changed the name from "Temp 11" to Range name in cell K1 the above formula now keeps looking for Sheet name "Temp 11" everytime I open the sheet. Could someone please help tell how to tell it to ignor if unable to find the sheet name "Temp 11". I am only new at this but I am a very persistant person and plan on learning as much as I can. Paul Maynard Moscow Russia. -- paulrm906 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lock tab in sheet 2 until cell in sheet one is completed | Excel Worksheet Functions | |||
Rename Sheet | Excel Discussion (Misc queries) | |||
How to get a row consecutive from a sheet to another | Excel Worksheet Functions | |||
How to rename Excel Sheet with more characters? | Excel Discussion (Misc queries) | |||
data from one sheet to several in sequential order! | New Users to Excel |