Sheet tab = Cell Value 1

[Wray69]

Hello all,

Have'nt been here in awhile. Not sure if this is even possible, if it is I know it will be VB, but;

Is there any way to link the sheet names to a particular cell that if that cell is changed it will change the sheet tab name?

Looking forward to your help.

Wray

 

[SkipVought]

Hi,

take a look at the CELL worksheet function
[tt]
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
[/tt]


Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Wray69]

Thanks, that changes the cell when you change the tab name, I need to change the tab name when the cell value changes. Is that possible?

Wray

 

[KenWright]

LOL - My first thought also Skip till I read it again

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Hi Wray, long time no see

I'm guessing you will need a change event macro tied to that cell.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Try this:-

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Put this in the worksheet code module (right-click on the worksheet
'tab, choose View Code, paste the code in the window that  opens, then
'click the XL icon on the toolbar to return to XL):
  
On Error GoTo ErrHandler:

  If Not Intersect(Target, Range("A1")) Is Nothing Then
    Application.EnableEvents = False
      ActiveSheet.Name = Target.Value
      Application.EnableEvents = True
  End If

ErrHandler:
  If Err.Number = 1004 Then
     MsgBox "You can't use an existing sheet's name, Try again!!"
     Application.EnableEvents = True
     Exit Sub
  End If
End Sub

Regards
Ken........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

oops

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[Wray69]

Thaks Ken, works like a charm... Thanks to you also skip. I might be going back into the tech side of the business here, if so I will be around more often.

Cheers,

Wray