Hyperlink > go to certain worksheet based on cell value

[SwiftM]

Hi all,

I am still learning with excel and have been rattling my brain over the past few days of how to get this to work! So here we go....

In worksheet "Log", cells C2, C3, C4 will have values inserted from a userform with numbers like 732, 733, 744 etc and these cells will be hyperlinked. I have managed to use VBA to give these values a hyperlink. However, what I am struggling with is to give these hyperlinks a go to address i.e. a worksheet in the workbook with relates to those numbers.

For example.

If C2 = 732 then when I click 732 hyperlink it takes me to worksheet "marcswi".

The idea is that whenever a specific value (732,733 etc) is inserted into column "C:C" then I can just click on that cell and it takes me to the relative worksheet in the workbook.

Is there some coding which could be implemented into worksheet "Log" which would achieve this?

I hope this makes sense and I am most grateful for any help you can give me .

Many thanks.

 

[mumps]

We would need a list of numbers and the name of their associated worksheet.

 

[SwiftM]

We would need a list of numbers and the name of their associated worksheet.

Thank you for the quick response, Mumps.

Numbers Worksheet
732 = marswi
764 = andrei
766 = chipol
715 = ryawee

These are the numbers and the relevant worksheets.

Hope this helps.

Kind regards,

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a number in column C and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Dim arr As Variant, i As Long
    arr = Array("732", "marswi", "764", "andrei", "766", "chipol", "715", "ryawee")
    For i = LBound(arr) To UBound(arr) Step 2
        If Target = CLng(arr(i)) Then
            Sheets(arr(i + 1)).Activate
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[SwiftM]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a number in column C and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Dim arr As Variant, i As Long
    arr = Array("732", "marswi", "764", "andrei", "766", "chipol", "715", "ryawee")
    For i = LBound(arr) To UBound(arr) Step 2
        If Target = CLng(arr(i)) Then
            Sheets(arr(i + 1)).Activate
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Hi Mumps,

This is brilliant thank you for your time

What I was looking for is to be able to click the cell where "732" or "764 etc is in worksheet "Log", and it takes me to the correct worksheet. The reason for this is because I have created a userform where the textbox.values (732 etc) are automatically inserted into Column C in worksheet "Log" after clicking the cmdSubmit button on the userform - rather than manually typing a number into column C and pressing enter. Does this make sense?

So in a nutshell, the userform automatically inserts the number, lets say 732 into the next available cell in column C in worksheet "Log". Then whenever I click onto that particular cell it takes me to the linked worksheet.

Many thanks again for helping me with this. Very much appreciated!

Kindest regards,

 

[mumps]

I would suggest that instead of a simple click, you use a double click. This way if you click on the cell accidentally, the macro won't be triggered. Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Dim arr As Variant, i As Long
    arr = Array("732", "marswi", "764", "andrei", "766", "chipol", "715", "ryawee")
    For i = LBound(arr) To UBound(arr) Step 2
        If Target = CLng(arr(i)) Then
            Sheets(arr(i + 1)).Activate
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

 

[SwiftM]

I would suggest that instead of a simple click, you use a double click. This way if you click on the cell accidentally, the macro won't be triggered. Try:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    Application.ScreenUpdating = False
    Dim arr As Variant, i As Long
    arr = Array("732", "marswi", "764", "andrei", "766", "chipol", "715", "ryawee")
    For i = LBound(arr) To UBound(arr) Step 2
        If Target = CLng(arr(i)) Then
            Sheets(arr(i + 1)).Activate
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Absolutely brilliant!!

Thank you, Mumps. This is exactly what I needed!!

I didn't think to use the DoubleClick option! Every day is a school day.....

Thanks again.

 

[mumps]

You are very welcome.