#1   Report Post  
Posted to microsoft.public.excel.misc
paulrm906
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
paul
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
lock tab in sheet 2 until cell in sheet one is completed john tempest Excel Worksheet Functions 7 November 24th 05 09:45 AM
Rename Sheet Linda@VZ Excel Discussion (Misc queries) 1 November 2nd 05 01:43 PM
How to get a row consecutive from a sheet to another rodante Excel Worksheet Functions 4 October 30th 05 07:20 PM
How to rename Excel Sheet with more characters? Billy316 Excel Discussion (Misc queries) 4 July 20th 05 05:11 AM
data from one sheet to several in sequential order! firecord New Users to Excel 6 June 22nd 05 05:10 PM


All times are GMT +1. The time now is 10:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"