Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

0 votes

I want the code to first determine whether a single cell is selected on the sheet I'm in, and then it should determine whether another sheet in the Workbook contains a value that matches the value in the selected cell.

I'm trying to make a hyperlink in the selected cell that refers to the sheet with the same name/value if these criteria are satisfied.

For instance, I want the code to generate a hyperlink in cell A1 to the sheet named "1000" if I select a cell (A1) with the value "1000" and there is a sheet named "1000".

selectedCell.Hyperlinks.Add Anchor:=selectedCell, Address:="", SubAddress:=selectedCell.Value & "!", TextToDisplay:=selectedCell.Value
Sub CreateHyperlinkToSheet()
    Dim selectedCell As Range
    Set selectedCell = Selection

    If selectedCell.Count <> 1 Then
        MsgBox "Please select a single cell.", vbExclamation
        Exit Sub
    End If

    If Not SheetExists(selectedCell.Value) Then
        MsgBox "No sheet exists with the name '" & selectedCell.Value & "'.", vbExclamation
        Exit Sub
    End If

    selectedCell.Hyperlinks.Add Anchor:=selectedCell, Address:="", SubAddress:=selectedCell.Value & "!", TextToDisplay:=selectedCell.Value
End Sub

Function SheetExists(sheetName As String) As Boolean
    On Error Resume Next
    SheetExists = (Len(Sheets(sheetName).Name) > 0)
    On Error GoTo 0
End Function

Feb 6, 2023 in Others by Kithuzzz
• 38,010 points
551 views

1 answer to this question.

0 votes

Credit to Spectral Instance who found the solution and wrote it in the comments;

selectedCell.Hyperlinks.Add Anchor:=selectedCell, Address:="#'" & selectedCell.Value & "'!A1", TextToDisplay:="" & selectedCell.Value & ""

The error message was due to the value of the cell beeing a number. In this case the cell A1 had the value of 1000.

answered Feb 6, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer

Excel VBA- How to loop through specific sheets in a workbook and format the same ranges in each sheet

Range(...) instructs VBA to always use the ...READ MORE

answered Mar 21, 2023 in Others by Kithuzzz
• 38,010 points
1,104 views
0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 63,420 points
727 views
0 votes
1 answer
0 votes
1 answer

How to insert a new row in the specified cell only, of Excel sheet using c#?

I have this worksheet with a matrix ...READ MORE

answered Nov 24, 2022 in Others by narikkadan
• 63,420 points
1,774 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
864 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
3,167 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 23,260 points
461 views
0 votes
2 answers

How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,670 points
719 views
0 votes
1 answer

VBA Export as PDF and Save to Location with name as per a Cell in the worksheet

Following is the code that gets generated ...READ MORE

answered Jan 20, 2023 in Others by narikkadan
• 63,420 points
1,340 views
0 votes
1 answer
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP