Search Bar and HyperLinks with VBA

timisissy

New Member
Joined
Feb 1, 2019
Messages
5
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:

Code:
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. :)
 
Last edited by a moderator:

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
Welcome to the forum.

I ALWAYS put Option Explicit in the beginning of code modules to avoid things like this:
Code:
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

New Member
Joined
Feb 1, 2019
Messages
5
Hi,

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,969
Office Version
  1. 365
Platform
  1. Windows
Where have you placed the code?
Is it in a regular module, Sheet module, ThisWorkbook Module?
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows

ADVERTISEMENT

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.
 
Last edited:

timisissy

New Member
Joined
Feb 1, 2019
Messages
5
In Worksheet: FollowHyperlink.
I don't know those you wrote. I just followed the video. :)
 

timisissy

New Member
Joined
Feb 1, 2019
Messages
5

ADVERTISEMENT

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

Well-known Member
Joined
Jul 14, 2010
Messages
1,394
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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

New Member
Joined
Feb 1, 2019
Messages
5
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,322
Messages
5,641,526
Members
417,213
Latest member
wikk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top