VBA Hyperlinks between Worksheets-CodePudding

I am working in a large workbook with multiple worksheets and I am trying to write a user form that will display all the worksheets that are in the workbook. I already have the code for identifying all the worksheets in the given workbook and this is already in a text field in a user form. The output is something like that:

................. Sheet 1 Page1
Sheet 2 Page2
Sheet 3 Page3
Sheet 4 Page4
Sheet 5 Page5
Sheet 6 Page6
Sheet 7 Page7
Sheet 8 Page8 \

......................

I want to insert a hyperlink in the string "Page number" so that when I click on the corresponding field, the corresponding worksheet is activated. I basically have no idea how to add a hyperlink to a string value without using a cell as a reference.

Here is the code I used to extract the corresponding sheet names and their index:

 Sub WorksheetLoop()

     Dim WS_Count As Integer
     Dim i As Integer
     Dim txt As Variant

     WS_Count = ActiveWorkbook.Worksheets.count
     
     For i = 1 To WS_Count
        vl = ActiveWorkbook.Worksheets(i).name
        If txt = "" Then
            txt = "Sheet " & i & "  " & vl
        Else
            txt = txt & vbCrLf & "Sheet " & i & "  " & vl
        End If
     Next i
     Debug.Print (txt)
  End Sub

Thx a lot for any help

CodePudding user response：

I think you would be better off using a list box instead of a textbox. Add a list box to the form as follows:

Use the default name of "Listbox1". Then add the following code to the userform:

Private Sub ListBox1_Click()
  ActiveWorkbook.Worksheets(ListBox1.Value).Select
End Sub

Private Sub UserForm_Initialize()
    Dim i As Integer
    
     For i = 1 To ActiveWorkbook.Worksheets.Count
        ListBox1.AddItem "Sheet " & i & " " & ActiveWorkbook.Worksheets(i).Name
     Next
End Sub

When you open the form, the listbox will contain a list of all the worksheets. Click on one to activate it. If you want to leave the userform open while you work on the worksheets, then open the form with the following line of VBA:

userform1.Show false

CodePudding user response：

FYI @Gove

Private Sub ListBox1_Click()
    
   Dim i, k As Integer
   Dim arr() As Variant
   Dim vl, vl2 As Variant
   
   WS_Count = ActiveWorkbook.Worksheets.Count
         
         k = 0
         For i = 1 To WS_Count
            vl = "Sheet " & i & " " & ActiveWorkbook.Worksheets(i).Name
            ReDim Preserve arr(k)
            arr(k) = vl
            k = k   1
         Next i

        
   vl2 = ListBox1.Value
   
    For i = LBound(arr) To UBound(arr)
        If vl2 = arr(i) Then
            j = i   1
            ActiveWorkbook.Worksheets(j).Select
            Debug.Print (i & "  " & vl2 & "  " & arr(i))
            Exit For
        End If
    Next i
    
End Sub