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.

 

[Nalani]

Quite nifty code

Yes it is !!

And I will be the first to give you the:

Pat on the back
Thumbs Up
"Atta Boy Award"
and the Kiss on the Cheek

<!-- / message --><!-- sig -->

 

[wigi]

Thanks for the heads up

The last addition (it's 2.30 AM in Belgium ):

if the specified module does not exist, it is created and the code jumps to this new empty module.

Sub MyProcedure_create_if_not_existing()

    On Error Resume Next
    Dim ProcKind As VBIDE.vbext_ProcKind
    sModule = InputBox("Please provide the module name", "Module name", "Module1")
    With ActiveWorkbook.VBProject
        If Len(.VBComponents(sModule).Name) = 0 Then .VBComponents.Add(vbext_ct_StdModule).Name = sModule
        With .VBComponents(sModule).CodeModule
            If .CountOfLines > .CountOfDeclarationLines Then
                'there is at least 1 procedure in the module
                Application.Goto .ProcOfLine(.CountOfDeclarationLines + 1, ProcKind)
            Else
                'there are no procedures in the module: make a temporary procedure, jump to it and remove the procedure again
                .InsertLines 1, "Sub TestProc()"
                Application.Goto "TestProc"
                .DeleteLines 1
            End If
        End With
    End With

End Sub

 

[Nalani]

Go get some sleep!

I will work with this and post back later. Probably for me, not until Monday.

Thanks again for your help.

 

[Nalani]

Okay....... I think I got it.

Placed your code in the Sheet as a DoubleClick event with a couple changes.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 On Error Resume Next
    Dim ProcKind As VBIDE.vbext_ProcKind
 
    sModule = ActiveCell.Value    '// minor adjustment here so to delete MsgBox
 
    ActiveSheet.Range("A1").Select   '//added because ActiveCell would not deactivate,
 ' causing not able to Activate Module without pressing Esc key.  Active cell still had focus.
 
       With ActiveWorkbook.VBProject
        If Len(.VBComponents(sModule).Name) = 0 Then .VBComponents.Add(vbext_ct_StdModule).Name = sModule
        With .VBComponents(sModule).CodeModule
            If .CountOfLines > .CountOfDeclarationLines Then
                'there is at least 1 procedure in the module
                Application.Goto .ProcOfLine(.CountOfDeclarationLines + 1, ProcKind)
            Else
                'there are no procedures in the module: make a temporary procedure, jump to it and remove the procedure again
                .InsertLines 1, "Sub TestProc()"
                Application.Goto "TestProc"
                .DeleteLines 1
            End If
        End With
    End With
End Sub

If you see something that could be improved, please do your thing.

Thanks again for your help.

 

[wigi]

I think that you do not understand and did not investigate what Target is doing in this (and related events).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
 On Error Resume Next
    Dim ProcKind As VBIDE.vbext_ProcKind

    sModule = Target.Value
 
       With ActiveWorkbook.VBProject
        If Len(.VBComponents(sModule).Name) = 0 Then .VBComponents.Add(vbext_ct_StdModule).Name = sModule
        With .VBComponents(sModule).CodeModule
            If .CountOfLines > .CountOfDeclarationLines Then
                'there is at least 1 procedure in the module
                Application.Goto .ProcOfLine(.CountOfDeclarationLines + 1, ProcKind)
            Else
                'there are no procedures in the module: make a temporary procedure, jump to it and remove the procedure again
                .InsertLines 1, "Sub TestProc()"
                Application.Goto "TestProc"
                .DeleteLines 1
            End If
        End With
    End With
End Sub

 

[Nalani]

Well, just running you latest posted code:

I still get Focus on the cell that was Double Cliked.

It does open the Module, but prevents me from Selecting that module unless I somehow select some other cell in the Sheet or pres Esc.

 

[wigi]

Well, just running you latest posted code:

I still get Focus on the cell that was Double Cliked.

It does open the Module, but prevents me from Selecting that module unless I somehow select some other cell in the Sheet or pres Esc.

Sorry, but I cannot reproduce it. With me it works perfectly.

 

[Nalani]

Your code does work perfectly as far as showing the Moduels.

I don't know if you had tried to Activate the Module once it was shown.
Your code did open the Module based off of the Cell Value.

Once it was opened, The cell that called it was still the active cell. Meaning that that there was a cursor in that cell, preventing any other actions.

With me putting in:

ActiveSheet.Range("A1").Select

Allowed me to work in the VBE on the Module. Without the above, I couldn't do anything unless I press Esc.

The Code you have worked on and provided is doing the job, so long as I select another cell other then the ActiveCell after sModule is declared.

 

[dk]

The event code should cancel the default action (which is to edit the cell) if it successfully opens the VBE. Something like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' On Error Resume Next
    Dim ProcKind As VBIDE.vbext_ProcKind
    Dim sModule As String
    Dim bCancelDefault As Boolean
    
    sModule = Target.Value
    
    'Assume that the default action does not need to be cancelled
    bCancelDefault = False

    With ActiveWorkbook.VBProject
        If Len(.VBComponents(sModule).Name) = 0 Then .VBComponents.Add(vbext_ct_StdModule).Name = sModule
        With .VBComponents(sModule).CodeModule
            If .CountOfLines > .CountOfDeclarationLines Then
                'there is at least 1 procedure in the module
                Application.Goto .ProcOfLine(.CountOfDeclarationLines + 1, ProcKind)
                bCancelDefault = True
            Else
                'there are no procedures in the module: make a temporary procedure, jump to it and remove the procedure again
                .InsertLines 1, "Sub TestProc()"
                Application.Goto "TestProc"
                .DeleteLines 1
                bCancelDefault = True
            End If
        End With
    End With
    
    'If the procedure has opened the VBE then cancel the default action (starts edit mode in the cell)
    Cancel = bCancelDefault
    
    
End Sub

Also, "On Error Resume Next" should be used carefully as it will often mask problems with the code. In my case I had to change the security setting "Trust Access to VBA Project" before the code would run correctly and as this is the default setting for an Excel installation it's likely that other users would have the same problem. Something like this might be preferable:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Dim ProcKind As VBIDE.vbext_ProcKind
    Dim sModule As String
    Dim bCancelDefault As Boolean

    On Error GoTo ErrHandler

    
    'Rest of code here
    '...
    '...
    

ErrHandler:
    If Err.Description Like "Programmatic access to Visual Basic Project*" Then
        MsgBox "Please adjust your security settings to allow access to VB project.", vbExclamation, "Error"
    Else
        MsgBox Err.Description, vbExclamation, "Error"
    End If


End Sub

HTH
DK

 

[wigi]

Nalani, DK,

1. Again, I still cannot reproduce the fact that Excel is in edit mode, and we need to select another cell first. For me and my (standard) installation of Excel, it is NOT.

2. About "Trust Access to VBA Project": that is what mentioned on Chip's page, so that should be set correctly prior to using the macro. Simply conveying the message to the user(s) that this is a prerequisite to use the code in a good way.