VBA create link to a module without running it 1

[Katto]

Hello,

I have an excel file with many macros so I sometimes have to spend too much time finding it.

I was able to create a list of the modules on a sheet.
Next, I would like to create hyperlinks, so that when I click (or double click) on the cell, it would bring me to the module in the Visual Basic Editor.
I found some routines that allow you to RUN the macro through a hyperlink. This is not my goal.
I just want to get to the module.

Is this possible?

Thank you

 

[combo]

Yes, it's possible. The macro below searches VBproject in the same excel workbook, input from InputBox, some search optional arguments not set. Required reference to VBIDE:

Sub SearchVBP()
Dim sToFind As String, lCurrLine As Long, lStartColumn As Long, bFound As Boolean
Dim VBC As VBIDE.VBComponent
sToFind = InputBox("string to find:")
For Each VBC In ThisWorkbook.VBProject.VBComponents
    lCurrLine = 1
    bFound = VBC.CodeModule.Find(target:=sToFind, startline:=lCurrLine, startcolumn:=1, endline:=-1, endcolumn:=-1)
    If bFound = True Then
        bFound = False
        If VBC.CodeModule.ProcOfLine(lCurrLine, vbext_pk_Proc) = sToFind Then
            MsgBox "Procedure " & sToFind & " found in " & VBC.Name & " line " & lCurrLine
            If ThisWorkbook.VBProject.VBE.MainWindow.Visible = False Then
                ThisWorkbook.VBProject.VBE.MainWindow.Visible = True
            End If
            VBC.CodeModule.CodePane.Show
            VBC.CodeModule.CodePane.SetSelection startline:=lCurrLine, startcolumn:=1, endline:=lCurrLine, endcolumn:=100
            Exit Sub
        End If
    End If
Next VBC
End Sub

combo

 

[Katto]

Thank you. Works great!

 

[Andrzejek]

How about a Star for combo? Click on [blue]Great Post![/blue] in his reply.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Katto]

There you go!

 

[SkipVought]

There you go!

Your "go" did not go to a Great post! to anyone!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!