Name of sheet from cell?

Barrie Davidson

MrExcel MVP

Joined

Feb 10, 2002

Messages

2,330

• Dec 17, 2005

• #2

Right click on the worksheet, select view code and paste this code in the module window.

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NameRange As Range
Set NameRange = Range("A1")
If Not Intersect(Target, NameRange) Is Nothing Then
    On Error Resume Next
    ActiveSheet.Name = NameRange
    If ActiveSheet.Name <> NameRange Then
        MsgBox "Unable to change name"
    End If
End If
End Sub

This will change the worksheet to the value in A1.

Does this help?

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Dec 17, 2005

• #3

Sans VBA --

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Note: The reference to A1 can be used anywhwere in the sheet, the formula just needs a/any reference to operate upon.

 

Upvote 0

Barrie Davidson

MrExcel MVP

Joined

Feb 10, 2002

Messages

2,330

• Dec 17, 2005

• #4

JJ - the OP is looking to change the sheet name to whatever's entered in an input cell (if I understand correctly).

 

Upvote 0

just_jon

Legend

Joined

Sep 3, 2002

Messages

10,473

• Dec 17, 2005

• #5

Barrie Davidson said:

JJ - the OP is looking to change the sheet name to whatever's entered in an input cell (if I understand correctly).

Click to expand...

Well, call me just_dense.

 

Upvote 0

D

damian_r_Home

Board Regular

Joined

Jan 8, 2005

Messages

231

Office Version

1. 365
2. 2007

Platform

1. Windows

• Dec 18, 2005

• #6

WOW!!!

Thanks for the help on this guys.

I'll give it a whirl over the next few days and see what i get.

Thanks again


Damian

 

Upvote 0

autigers

Board Regular

Joined

Oct 9, 2005

Messages

139

• Dec 18, 2005

• #7

It doesn't like a date ..... ex ... 12-18 returns "Unable to change name"

 

Upvote 0

D

damian_r_Home

Board Regular

Joined

Jan 8, 2005

Messages

231

Office Version

1. 365
2. 2007

Platform

1. Windows

• Dec 18, 2005

• #8

Just tried it out and what can i say - FANTASTIC!!!!!


Thanks

DAmian

 

Upvote 0

Barrie Davidson

MrExcel MVP

Joined

Feb 10, 2002

Messages

2,330

• Dec 19, 2005

• #9

autigers said:

It doesn't like a date ..... ex ... 12-18 returns "Unable to change name"

Click to expand...

How about changing to

Code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim NameRange As Range
Set NameRange = Range("A1")
If Not Intersect(Target, NameRange) Is Nothing Then
    On Error Resume Next
    ActiveSheet.Name = CStr(NameRange)
    If ActiveSheet.Name <> NameRange Then
        MsgBox "Unable to change name"
    End If
End If
End Sub

 

Upvote 0

D

damian_r

Active Member

Joined

May 4, 2004

Messages

389

• Dec 19, 2005

• #10

HI again.

If I add a value to the target cell that is alphabetical, everything works fine.
If I add a value to the target cell that is alphabetical then numerical (or vies versa), everything works fine.
However, if you enter just a numerical value, the sheet name does change even though you get an error message popping up. Can we either make the numerical value work so the pop up error message doesn’t happen, or perhaps turn off the error message?

Your help again would be appreciated.


Damian

 

Upvote 0