Search Bar and HyperLinks with VBA

[timisissy]

Hi all,

I'd need some help. I have the following VBA, and everything is working properly except the simplest thing: the automtical hide-unhide part I saw from Mr.Excel:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    LinkTo = Target.SubAddress
    WhereBang = InStr(1, LinkTo, "!")
    If WhereBank > 0 Then
        MySheet = Left(LinkTo, WhereBang - 1)
        Worksheets(MySheet).Visible = True
        Worksheets(MySheet).Select
        Myaddr = Mid(LinkTo, WhereBang + 1)


        Worksheets(MySheet).Range(Myaddr).Select
End If
End Sub

I am wondering if the other VBAs could confuse it? Actually If I hide the sheets, and try to clik on the Link, nothing happenes. :( 

See the full VBA here:

Private Sub SEARCH_Click()
Dim lastrow As Long, myentry As Long
lastrow = Sheets("item_price").Cells(Rows.count, 1).End(xlUp).Row
'MsgBox lastrow
'Sheets("item_price").Activate
 For X = 2 To lastrow
' myentry = Sheet2.Range("B3").Value
 'MsgBox myentry
 If Sheets("item_price").Cells(X, 1).Value = Sheet2.Range("B3").Value Then
 Sheet2.Range("A11").Value = Sheets("item_price").Cells(X, 1).Value
  Sheet2.Range("B11").Value = Sheets("item_price").Cells(X, 2).Value
   Sheet2.Range("C11").Value = Sheets("item_price").Cells(X, 3).Value
   Sheet2.Range("D11").Value = Sheets("item_price").Cells(X, 4).Value
   Sheet2.Range("E11").Value = Sheets("item_price").Cells(X, 5).Value
   End If
   Next X
   End Sub


Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    LinkTo = Target.SubAddress
    WhereBang = InStr(1, LinkTo, "!")
    If WhereBank > 0 Then
        MySheet = Left(LinkTo, WhereBang - 1)
        Worksheets(MySheet).Visible = True
        Worksheets(MySheet).Select
        Myaddr = Mid(LinkTo, WhereBang + 1)


        Worksheets(MySheet).Range(Myaddr).Select
End If
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2018/9/21
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
     
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

Please note, that this is my very first VBA ever, and I'd need it for work. That is my main project I would like to fulfill.

Thank you very much for your help!!!

I wish you all a lovely day!

Timi from Hungary.

 

[bobsan42]

Welcome to the forum.

I ALWAYS put Option Explicit in the beginning of code modules to avoid things like this:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    LinkTo = Target.SubAddress
    WhereBang = InStr(1, LinkTo, "!")
    If WhereBan[COLOR=#ff0000][B]g[/B][/COLOR] > 0 Then
        MySheet = Left(LinkTo, WhereBang - 1)
        Worksheets(MySheet).Visible = True
        Worksheets(MySheet).Select
        Myaddr = Mid(LinkTo, WhereBang + 1)




        Worksheets(MySheet).Range(Myaddr).Select
End If
End Sub

 

[timisissy]

Hi,

oh, I see now. Thank you! Unfortunately, no matter how hard I click with the mouse, still nothing happens.

 

[Fluff]

Where have you placed the code?
Is it in a regular module, Sheet module, ThisWorkbook Module?

 

[bobsan42]

Also the cell you are clicking on must be an actual hyperlink for the event to work.
and the procedure code must be in the sheet code module.

 

[timisissy]

In Worksheet: FollowHyperlink.
I don't know those you wrote. I just followed the video.

 

[timisissy]

It is a Hyperlink already. originally I created a button for that, but I have tried with a basic Text as well, but none of them unhide the requested sheet.

 

[bobsan42]

Does the hyperlink point to a cell on the hidden sheet? (because it must for this to work)
Does the code run at all ?
A hyperlink means a Hyperlink inserted in the cell - not a hyperlink formula/function - this will not trigger the event.

Try to debug yourself:
open the code in the VB editor. Click on the grey field on the left of the code (next to the first line) - a dot must appear in the grey area - a break point.
Now click on the hyperlink - the execution of the code will stop on the line with the dot if the code runs at all. The active code line will be marked in yellow.
When you press F8 the line will be executed and the marker will move to next line.
Once you get to the line that says Worksheets(MySheet).Visible = True move you mouse pointer over MySheet and way for a tooltip to appear - this must be tha name of the sheet you need to be shown. If it is different - your hyperlink point to a wrong location.


Or:
Unhide all sheets, remove the Follow_Hyperlink code, click on the hyperlink and see where it takes you.

 

[timisissy]

Thank you guys! Unfortunately I couldn't find the issue. The Hyperlinks are working properly without the VBA. It would be amazing if I could automatically hide the sheets, though it is not that important as the other part of the VBA.

Thank you again!

p.s.: If you have any other idea, please don't hesitate to share with me.