Forum Discussion
Alphabet sort and worksheet sort
Hi, I have a workbook with multiple worksheets and was kindly given some code that enables me to type a name in worksheet 1 (called Training Matrix) and this will change the name on the worksheet...
Please replace the existing code with the following one and see if that works as desired...
Dim wsName As String
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim ws As Worksheet
On Error GoTo Skip
If Not Intersect(Target, Range("SheetNames")) Is Nothing Then
Application.EnableEvents = False
If Target <> "" Then
If wsName = "" Then
Set ws = Worksheets.Add(after:=Worksheets(Worksheets.Count))
ws.Name = Target.Value
Else
Set ws = Worksheets(wsName)
ws.Name = Target.Value
End If
End If
wsName = ""
End If
Application.EnableEvents = True
Exit Sub
Skip:
Application.Undo
Application.EnableEvents = True
MsgBox Err.Description, vbExclamation, Err.Number
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("SheetNames")) Is Nothing Then
Application.EnableEvents = False
wsName = Target.Value
Application.EnableEvents = True
End If
End Sub
Hi,
Thank you but this does not quite achieve what I want. It creates the worksheet with the new name but does not delete the worksheet or the worksheet name if I delete it from "Training Matrix".
Also if I add a name to the bottom of the list on "Training Matrix" and then sort alphabetically it does not reorder the worksheets alphabetically, is this possible?
Cheers
Julian
Hi Julian,
Sorry, I missed that requirement.
Please test the attached and let me know if it works as desired now.
