Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

VBA create link to a module without running it 1

Status
Not open for further replies.
Katto

Katto

Technical User
Joined
Apr 6, 2005
Messages
42
Location
US
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
 
Sort by date Sort by votes
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:
Code: 
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
 
Upvote 0 Downvote
Thank you. Works great!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
There you go!
 
Upvote 0 Downvote
katto said:
There you go!
Click to expand...

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

Skip,

[glasses]Just traded in my OLD subtlety...
for a NUance![tongue]
"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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

rj771972
  • Question Question
Powerpoint VBA - Assign manipulate object
Replies
1
Views
187
rj771972
rj771972
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
Andrzejek
  • Locked
  • Question Question
Outlook VBA - Trust access
Replies
3
Views
1K
hermanlaksko
hermanlaksko
thanhthanh12
  • Locked
  • Question Question
Help vba
Replies
3
Views
897
combo
combo
SLW89
  • Locked
  • Question Question
VBA newbie needs help
Replies
4
Views
822
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top