Hyperlink to Modules

[Nalani]

Is it possible to Hyperlink to a Module?

I have a list of all my Modules.
Module1, Module2, etc. (of course with meaningful names)

What I'm looking for is if there is a way to click on Module1 from the List, it will open that module in the VBE for whatever I want to do with it, read, modify, etc.

I've done some searching but only found a way to Run it. Don't want to Run, just Open for Viewing/Modification.

 

[GTO]

Greetings Nalani,

I thought there might be 2 types of errors:
1. What dk mentioned about Access to the VBA Project
2. In case a blank cell was clicked

While it looks to me that you are wanting just to 'link' to modules in ThisWorkbook, the use of ActiveWorkbook in Wigi's code got me to thinking... (a little scary in itself)

Anyways, here's a little different take. Not well tested, but seems to catch whether project access is trusted, or if the project is protected (if checking another wb), or if the module does not exist (or a blank cell is double-clicked).

As long as you just want to open the module and do not need it to go to a certain propcedure, this will open a Class or Object module, whereas GoTo seems to give me issues.

Option Explicit
    
Private Enum vbaConsts
    vbext_pp_locked = 1
    vbext_pp_none = 0
    vbext_ws_Maximize = 2
    vbext_ws_Minimize = 1
    vbext_ws_Normal = 0
End Enum
    
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim wb                  As Workbook
Dim strMsg              As String
Dim md                  As Object '<-- VBComponent
Dim bolFoundModule      As Boolean
Dim bolWBMissing        As Boolean
    
    '// However you wanted to limit the range...                                        //
    If Target.Column = 1 And Target.Row > 1 _
    And Target.Row < 20 Then
        
        Cancel = True
        '// If I was going to actually check other wb's, I'd probably throw from here   //
        '// down into a function                                                        //
        On Error Resume Next
        'Set wb = Workbooks("xxxTempTestLockedProject(EmptyProject).xls")
        Set wb = ThisWorkbook
        bolWBMissing = CBool(Err)
        On Error GoTo 0
        
        If Not bolWBMissing Then
            If ProjectAccessPossible(wb, strMsg) Then
                
                For Each md In wb.VBProject.VBComponents
                    If md.Name = Target.Text Then
                        '// I chose to use .CodePane, as this seems to work for both    //
                        '// Standard and Class modules.                                 //
                        wb.VBProject.VBComponents(Target.Text).VBE.MainWindow.Visible = True
                        wb.VBProject.VBComponents(Target.Text).CodeModule.CodePane.Show
                        wb.Application.VBE.MainWindow.WindowState = vbext_ws_Maximize
                        wb.Application.VBE.ActiveWindow.WindowState = vbext_ws_Maximize
                        bolFoundModule = True
                        Exit For
                    End If
                Next
                
                If Not bolFoundModule Then
                    MsgBox "I was unable to find a module named: " & Selection.Value, vbInformation, vbNullString
                End If
            Else
                MsgBox strMsg, vbCritical Or vbOKOnly, "Error!"
            End If
        Else
            MsgBox "Unable to reference wb...", vbInformation, vbNullString
        End If
    End If
    
    '...other statements...
End Sub
    
Private Function ProjectAccessPossible(ByVal wb As Workbook, ByRef MsgString As String) As Boolean
Dim lErrorCheck         As Long
Dim bolAccessTrusted    As Boolean
    
    '// Seems to catch whether access is trusted, while not being fooled if the project //
    '// is simply locked.                                                               //
    On Error Resume Next
    bolAccessTrusted = CBool(Len(wb.Parent.VBE.MainWindow.Caption))
    On Error GoTo 0
    
    '// If access is not trusted, the elseif doesn't get evaluated, so no foul.         //
    If Not bolAccessTrusted Then
        MsgString = "Reference: " & wb.Name & vbCrLf & vbCrLf & _
                    "Programmatic Access to Visual Basic Project is not trusted" & vbCrLf & _
             Space(4) & "This is an application controlled option and must be set by you."
    ElseIf wb.VBProject.Protection = vbext_pp_locked Then
        MsgString = wb.Name & "'s VBProject is locked for viewing."
    '// Just clarity; I think an Else would be fine.                                    //
    ElseIf bolAccessTrusted And wb.VBProject.Protection = vbext_pp_none Then
        ProjectAccessPossible = True
    End If
End Function

Hope that helps,

Mark

 

[snb_]

This suffices:

Sub Snb()
  Application.VBE.ActiveVBProject.VBComponents(InputBox("Please provide the module name", "Module name", "Module1")).Activate
End sub