CODEWITHSUNDEEP
vbaexcelhyperlinkexcel-2010
David Kris
David Kris

Reputation: 661

Hyperlink to new workbook broken

I'm new to Excel so I hope this makes sense. The code below shows a new sheet being created on a specific workbook (separate from the current one) when a button on a user form is clicked. Though, my hyperlink to the sheet created on the separate workbook seems to be broken. What am I doing wrong? Anything helps, thanks!

Dim LastRow As Long, ws As Worksheet
Set ws = Sheets("Employee Information")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

If Me.cbStores.Value = "Northern" Then
Dim newWB As Workbook
Dim thisWB As Workbook
Set thisWB = ThisWorkbook
Set newWB = GetOrCreateWB("EmployeeTemplates", "C:\Users\...\Folder") '<--| Opening EmployeeTemplates wb
thisWB.Sheets("Template").Copy after:=newWB.Sheets(1)
With ActiveSheet '<--| the just pasted worksheet becomes the active one
    .Name = AddEmployeeUF.txtFirstname.Text + AddEmployeeUF.txtMiddleinitial.Text + AddEmployeeUF.txtLastname.Text + "Template" '<--| Name it
    ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), Address:="", SubAddress:=.Name & "!A1", TextToDisplay:="View" '<--| hyperlink to new sheet
End With
End If

Upvotes: 0

Views: 95

Answers (2)

David Kris
David Kris

Reputation: 661

Someone helped me find a proper solution, so here it is:

ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), Address:=newWB.Path & "\" & newWB.Name, SubAddress:="'" & .Name & "'!A1", TextToDisplay:="View" '<--| hyperlink to new sheet

Upvotes: 0

Brent
Brent

Reputation: 61

Answer 01

This answer uses the file path such as "C:\Users\Me\Desktop\newfile.xlsx" 

With ActiveSheet '<--| the just pasted worksheet becomes the active one
    .Name = AddEmployeeUF.txtFirstname.Text + _
            AddEmployeeUF.txtMiddleinitial.Text + _
            AddEmployeeUF.txtLastname.Text + "Template" '<--| Name it

    ' the hyperlink SubAddress needs a valid file path or hyperlink to
    ' work like "C:\User\me\Desktop\newfile.xlsx" 
    ' .Name & "!A1" references a cell not the file location on the computer 
    ' or network
    'ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
    '                  Address:="", SubAddress:=.Name & "!A1", _
    '                  TextToDisplay:="View" '<--| hyperlink to new sheet

    ' you need something like this
    ' as long as newWB.Path property is set you should be good
    ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
                      Address:="", SubAddress:=newWB.Path, _
                      TextToDisplay:="View" '<--| hyperlink to new sheet
End With

Answer 02

This answer uses a reference which is what you originally wanted. I'm having trouble finding the link to the stack overflow question but I tested the code and it works. I liked this option but it only works as long as the workbook is in the same Excel application. If you have two applications open it will not work because there is no reference to the new workbook in the application.

With ActiveSheet '<--| the just pasted worksheet becomes the active one
    .Name = AddEmployeeUF.txtFirstname.Text + _
            AddEmployeeUF.txtMiddleinitial.Text + _
            AddEmployeeUF.txtLastname.Text + "Template" '<--| Name it

    ' the hyperlink SubAddress needs a valid file path or hyperlink to
    ' work like "C:\User\me\Desktop\newfile.xlsx" 
    ' .Name & "!A1" references a cell not the file location on the computer 
    ' or network
    'ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
    '                  Address:="", SubAddress:=.Name & "!A1", _
    '                  TextToDisplay:="View" '<--| hyperlink to new sheet

    ' you need something like this
    ' as long as newWB.Path property is set you should be good
    ws.Hyperlinks.Add Anchor:=ws.Range("F" & LastRow), _
                      Address:="", SubAddress:="'" & .Name & "'!A1", _
                      TextToDisplay:="View" '<--| hyperlink to new sheet
End With

Upvotes: 1

Related Questions