How to hyperlink cell value to userform created worksheet?

**jwichern1** · 02-25-2011, 09:26 AM

I have created a user form that will create a new sheet to match the EmployeeName entered and then on a different sheet copy that EmployeeName to a cell. I would like the code to make the new cell hyperlink to the sheet with the same name. Below is a copy of my existing code:

'Based on user selection from form
    If Specialty = True Then
'unhides template and then copies to a new sheet
        Sheets("Specialty").Visible = True
        Sheets("Specialty").Copy Before:=Sheets("Specialty")
        Sheets("Specialty (2)").Select
        Range("G1").Value = EmployeeNumber.Value
        Range("B2").Value = StartDate.Value
'renames new sheet to match the Employee Name entered on the user form
        Sheets("Specialty (2)").Select
        Sheets("Specialty (2)").Name = EmployeeName
        Sheets("Specialty").Select
        Sheets("Specialty").Visible = False
'selects snapshot worksheet and copies the employee name into a cell
        Sheets("SnapShot").Select
            Range("Data3").Select
            Selection.EntireRow.Hidden = False
            Range("Data3").Select
            Selection.Copy
            ActiveCell.Offset(1).EntireRow.Insert
            ActiveCell.Offset(1, 0).Select
            'paste the copied data in there
            Range("reHide3").Select
            ActiveCell.Offset(1, 0).Select
             
'This is where I need help 
'I need this new cell value to hyperlink to the sheet that was just created above that has the same name.  
      
            ActiveCell.Value = EmployeeName
                       
            Range("Data3").Select
            Selection.EntireRow.Hidden = True
            Application.CutCopyMode = False
            Range("C35").Select
            
        Sheets("Welcome").Select
        Unload Me
    End If

I hope what I am asking for is clear. Any help would be greatly appreciated.

Thanks.

**Andy Pope** · 02-25-2011, 09:40 AM

ActiveCell.Formula = "=HYPERLINK(""#" & EmployeeName & "!A1"",""" & EmployeeName & """)"

**jwichern1** · 02-25-2011, 10:11 AM

Andy,

Thank you for your quick response. However, I am getting an error message saying "Reference not valid" when I click the link. Do you have any suggestions on how to correct this?

Edit - It appears the issue is caused because the name entered has a first and last name with a <space> between them. Is there a fix for this?

Thank you,
John

**Andy Pope** · 02-25-2011, 10:24 AM

wrap the sheet name in single quotes.

ActiveCell.Formula = "=HYPERLINK(""#'" & EmployeeName & "'!A1"",""" & EmployeeName & """)"

**jwichern1** · 02-25-2011, 10:38 AM

That's perfect. Thank you very much for your help.

**J1s** · 06-10-2011, 12:37 PM

I am attempting to have Excel VBA identify the name in the active cell and hyperlink that cell to a sheet within the same workbook that has the same name as the cell? Is this possible? If so could some one walk me through how to write such code?

Thx, J