Worksheet names to cell

[bayofpigs]

Is there a way to have your worksheet name linked to a specific cell so the name of the sheet changes with the changes to the cell? Is there a way both using a vba code and/or non-code?

Thx

 

[Smitty]

Here you go. The code assumes that the sheet name will be in cell A1. It will also check to see if the sheet name already exists and kick you out if it does.

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Function</SPAN> SheetExists(SheetName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <SPAN style="color:#007F00">' Returns TRUE if a sheet exists in the active workbook</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> X <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>            <SPAN style="color:#00007F">Set</SPAN> X = ActiveWorkbook.Sheets(SheetName)<br>                <SPAN style="color:#00007F">If</SPAN> Err = 0 <SPAN style="color:#00007F">Then</SPAN> SheetExists = True _<br>                <SPAN style="color:#00007F">Else</SPAN> SheetExists = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><SPAN style="color:#007F00">'   Code goes in the ThisWorkbook module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("A1")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> SheetExists(Target.Value) <SPAN style="color:#00007F">Then</SPAN><br>                MsgBox "This sheet name already exists, please try another", vbCritical + vbOKOnly, "Sheet Name Already Exists!"<br>                    <SPAN style="color:#00007F">With</SPAN> Target<br>                        .Activate<br>                        Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN><br>                            .Value = ""<br>                        Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN><br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>            <SPAN style="color:#00007F">Else</SPAN><br>                Sh.Name = Target.Value<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

HTH,

 

[Rick Rothstein]

You can use this event code for the particular worksheet you want to have this functionality (it assumes the "specific cell" that will be monitored is cell A1, change as needed)...

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Target.Address(0, 0) = "A1" Then Target.Parent.Name = Range("A1").Value
  If Err.Number Then MsgBox "Your entry is not valid for a sheet name!"
  On Error GoTo 0
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[bayofpigs]

Thanks for your help. Will it work if the cell was on a different sheet?

 

[Rick Rothstein]

Thanks for your help. Will it work if the cell was on a different sheet?

Not sure what you mean by that... the instructions I gave you tell you to right-click the tab for the worksheet you want to have this functionality... what "different sheet" do you mean?

 

[bayofpigs]

What I mean is for the worksheet name, for example sheet "x", is linked to a cell in a different sheet, say "y".