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,