First, one of the things JE's suggested code was to clearcontents of the cell
whose worksheet was deleted.
I'd change this line:
rResult(1).ClearContents
to
rResult(1).entirerow.delete
to delete the whole row.
Then there would be no gaps in the data. And any new name could be added to the
bottom of the list.
And I like to use the =hyperlink() worksheet function in an adjacent cell
(another reason to delete the entire row!).
The worksheet function =hyperlink() will react nicely when/if you change the
name of that worksheet.
A lot of things could go wrong with this type of thing. The user could enter
the name of a worksheet that already exists--or could enter an invalid name
(wrong characters like slashes, colons... or too many characters).
I made some assumptions. You'll have to change them if I guessed wrong.
Option Explicit
Sub AddName()
Dim myNewName As String
Dim ActWks As Worksheet
Dim NewWks As Worksheet
Dim DestCell As Range
myNewName = Trim(InputBox(Prompt:="Enter a new name"))
If myNewName = "" Then
Exit Sub
End If
Set ActWks = ActiveSheet
With ActWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With
Application.ScreenUpdating = False
With Worksheets("template")
.Visible = xlSheetVisible
.Copy after:=Worksheets(Worksheets.Count)
Set NewWks = ActiveSheet
.Visible = xlSheetHidden
End With
On Error Resume Next
NewWks.Name = myNewName
If Err.Number <> 0 Then
MsgBox "Error renaming the new sheet from: " _
& NewWks.Name & " to: " & myNewName & vbLf _
& "Could be an invalid name or a sheet" _
& " by that name already exists!" _
& vbLf & vbLf & "Entered Name NOT Used--Worksheet Name Added!!!"
Err.Clear
End If
'put the name entered so the hyperlink works?????
DestCell.Value = NewWks.Name
DestCell.Offset(0, 1).Formula _
= "=HYPERLINK(""#""&CELL(""address"",INDIRECT(""'""&" _
& DestCell.Address(0, 0) & "&""'!A1"")),""Click Me"")"
ActWks.Activate
Application.ScreenUpdating = True
End Sub
Dave:
I don't want to over extend my welcome, but I also am really looking
for reversal of this code too. What I mean is that if the user wants
to add a student. Now this is a bit tricky. Because lets say in
column A starting at A2 the names start, Assume we have continuous
names in range A2:A20. Now with the help of previous code the user is
able to delete lets say one of the names (A10). Now if we want to add
a name it would be nice to start from bottom where ever that is in this
case A20 come up and find our null(A10) and insert the name there.
Once we insert the name, the macro will create a tab for the inserted
name by copying a hidden tab called "Template" and make a link from the
name to the newly created tab. I probably can peace mill the copying
and linking, but I am stump on the finding the null, and what if there
is no gap I assume there is an else in there to make it go to the
bottom of the list.
Ardy