Excel VBA: Creating a link to the new Sheet when created

[RobWulf]

I am trying to create a link to a sheen when I create a new worksheet in my meeting book, is there anyway to add a link
when I create a new sheet? I have found a few codes that allow the cration of an index page, but that is not what I am trying to do and creating a macro didn't work too well, or I may have edited it wrong.

Macro
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        "'Meeting.45'!A1"
End Macro

This is the code I use for the Form the button is located on. Requires a page called Template that is hidden. This part works great in creating a new line and setting up the sheets for me, but I want to create a link at the same time.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Schedule")
Dim TopicID As Integer
Dim txtProj As Integer
Dim Sig As String
Dim Fid As String
Dim x As Integer
 
'''find  first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(0, 0).Row
'revised code to avoid problems with Excel tables in newer versions
 
'check for a part number
If Trim(Me.txtTitle.Value) = "" Then
  Me.txtTitle.SetFocus
  MsgBox "Please enter a Title for this Meeting Session"
  Exit Sub
End If


fis = iRow

'Sum = 1 + Me.TopicID.Value
'copy the data to the database
ws.Cells(iRow, 1).Value = fis
ws.Cells(iRow, 3).Value = "No"
ws.Cells(iRow, 5).Value = txtTitle.Column(1)
ws.Cells(iRow, 7).Value = Me.txtLoc.Value
ws.Cells(iRow, 4).Value = Me.txtDate.Value
ws.Cells(iRow, 11).Value = Me.Lead.Value
ws.Cells(iRow, 9).Value = Me.txtSub.Value
ws.Cells(iRow, 6).Value = txtTitle.Column(0)
ws.Cells(iRow, 2).Value = txtTitle.Column(2) 'Me.TopicID.Value 'session id
ws.Cells(iRow, 12).Value = Me.txtAtty.Value
ws.Cells(iRow, 13).Value = Me.txtOCA.Value
ws.Cells(iRow, 14).Value = Me.CBClaim.Value
ws.Cells(iRow, 16).Value = "Open"
ws.Cells(iRow, 10).Value = "Location"

Sig = Me.txtTitle.Column(1)
Fid = Me.txtDate.Value
Dig = txtTitle.Column(0) & "." & txtTitle.Column(2)
Fig = txtTitle.Column(0)
nGD = txtTitle.Column(3)

'clear the data
Me.txtTitle.Value = ""
Me.txtLoc.Value = ""
Me.txtDate.Value = ""
Me.txtSub.Value = ""
Me.Lead.Value = ""
Me.txtAtty.Value = ""
Me.txtOCA.Value = ""
Me.CBClaim.Value = ""

'Me.txtTitle.SetFocus
 

Sheets("Template").Visible = True
Sheets("Template").Copy After:=Sheets("Schedule")
Sheets("Template").Visible = False
 
'Copies the FIS Number as the new sheet name.
ActiveSheet.Name = Dig 'Changed to match new FIS Structure.
ActiveSheet.Range("C1") = Me.txtAttend.Value
ActiveSheet.Range("C2") = Fid
ActiveSheet.Range("H2") = Fig
ActiveSheet.Range("J1") = Sig
ActiveSheet.Range("U2") = nGD
Me.txtAttend.Value = ""
'----------------------------------------
'Hyperlink Section to be added later
'----------------------------------------
 
EntryForm.Hide
 
End Sub

 

[RobWulf]

Good Morning Folks! After a bit more looking after a good night's sleep I was able to find this thread Vba Hyperlink To Local Worksheet

So after fiddling with it for a bit, I have a working solution.

'----------------------------------------
'Hyperlink Section
'----------------------------------------
strHyper2 = Dig + "!A1"

With Worksheets(1)
    .Hyperlinks.Add Anchor:=.Cells(iRow + 0, 5), Address:="", _
    SubAddress:=strHyper2, TextToDisplay:=(Sig)
End With

'----------------------------------------

THis creates a link using the name of our meeting room, and sets it in the spot where the meeting topic would be. If you change the 0 it will control how many rows down it goes and changing the 5 will alter what column the text appears in.