VBA Code to Add Hyperlink

[hollynordenberg]

Hello!

I created a "button" in an excell worksheet to create a new worksheet and add the new worksheet to a list (in the same worksheet as the button). The code is below. I am trying to add the function to add a hyperlink to the new row to link to the new sheet. Everything I try either does not hyperlink to any sheet, or the hyperlink links to the incorrect sheet. I am looking for suggestion!


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Address = "$A$1" Then

Call Button10_New

Call Fill_Row

End If

End Sub

Private Sub Button10_New()

Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)

On Error GoTo Handler

Sheets("template (2)").Name = Format(Range("LastRecord"))

Exit Sub

Handler:

MsgBox "Can Not Rename Sheet", vbCritical, "Error"

End Sub

Sub Fill_Row()

Sheets("Report").Activate

ActiveSheet.Cells(Range("LastRecord") + 3, 1) = Cells(3, 17)

End Sub

 

[Luke M]

There's a lot more code there than just the simple copy a worksheet bit, but I think this is what you want.

[pre]

Private Sub Button10_New()
Dim NewName As String
Dim ActualName As String
Dim LinkedCell As Range
Worksheets("Template").Copy After:=Worksheets(Worksheets.Count)
NewName = Range("LastRecord").Value
On Error GoTo Handler
ActiveSheet.Name = NewName
On Error GoTo 0
'Able to create hyperlink even if name method fails
ActualName = ActiveSheet.Name

'Where does the hyperlink need to go?
Set LinkedCell = Me.Cells(3, 17)

'Create the hyperlink
Me.Hyperlinks.Add Anchor:=LinkedCell, Address:="", SubAddress:= _
ActualName & "!A1", TextToDisplay:=ActualName
'Go back to sheet with button
Me.Activate
Exit Sub
Handler:
MsgBox "Can Not Rename Sheet", vbCritical, "Error"
Resume Next
End Sub

[/pre]