Rename Tabs Based on a Contents Page

[G16LYB]

Hi

Apologies I know this has been asked before but I cant find anything that seems to cover this specifically.

I have a workbook that I want to have a contents page with a reference number, I've then linked this reference to another sheet in cell A1. I've found some basic code that renames the tab, but if the reference changes in the contents it doesn't update the sheet unless you go into it and click into a cell.

I'll need a lot of tabs in the workbook, possibly 200 with the ability to add more if required.

I'm pretty proficient with excel, but I've zero experience with VBA so detailed instructions would be really helpful.

Thanks

 

[G16LYB]

I've recreated the whole workbook to make sure there wasnt old bits of code hanging around. Now when I change anything in column F I get the Sheet (whatever) Does not exist

 

[Fluff]

That means there was no sheet with the name of whatever was in col F before you changed it.
What should happen in that situation?

 

[G16LYB]

The sheets are just named by default at the moment Sheet4 Sheet5 etc. I want them to update based on whatever is put in column F.

I tried to get round it by naming column F to match the sheet numbers, then change column F, but that didnt work.

Now I dont even get error messages, just nothing happens

 

[Fluff]

A macro does not know what sheet name to change unless you tell it.
Try running this & then try changing a cell in col F

Sub reset()
   Application.EnableEvents = True
End Sub

 

[Fluff]

Also scrap the code I gave before & use this instead

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim OldName As String, NewName As String

   If Target.CountLarge > 1 Then Exit Sub
   If Not Intersect(Target, Range("F2:F500")) Is Nothing Then
      If Target.Value = "" Then Exit Sub
      Application.EnableEvents = False
      NewName = Target.Value
      Application.Undo
      OldName = Target.Value
      Target.Value = NewName
      If OldName <> "" And NewName <> "" Then
         If Not Evaluate("isref('" & OldName & "'!a1)") Then
            MsgBox "Sheet " & OldName & " does not exist"
         ElseIf Evaluate("isref('" & NewName & "'!a1)") Then
            MsgBox "Sheet " & NewName & " already exists"
         Else
            Sheets(OldName).Name = NewName
         End If
      End If
   End If
   Application.EnableEvents = True
End Sub

 

[G16LYB]

OK it changed one tab (f3), but wouldn't change F2 or F4, and it wont change it again. It came up with an error I didnt catch then pointed at this bit
Sheets(OldName).Name = NewName

 

[Fluff]

Was that with the original code I posted, or the code in post#25?

 

[G16LYB]

The code in #25

 

[Fluff]

In that case run the reset sub again & then change a cell in col F. If you get any error messages, make a note of what it says & post that back here.