×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA create link to a module without running it

VBA create link to a module without running it

VBA create link to a module without running it

(OP)
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

RE: VBA create link to a module without running it

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

RE: VBA create link to a module without running it

(OP)
Thank you. Works great!

RE: VBA create link to a module without running it

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

---- Andy

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

RE: VBA create link to a module without running it

(OP)
There you go!

RE: VBA create link to a module without running it

Quote (katto)

There you go!

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

Skip,

glassesJust 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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close