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.
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.
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.
Code:
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.
Code:
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
Last edited: