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

RobWulf

Board Regular
Joined
Apr 19, 2013
Messages
76
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.

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:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
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.

Code:
'----------------------------------------
'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.
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,915
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top