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.

 

[dk]

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.

Hi Wigi

Regarding point 1, what happens if you double-click a cell normally (i.e. without any VBA code running)? For me (and also the OP it appears) double-clicking in the cell is the same as pressing F2 in the cell (edit mode). Setting the Cancel argument to true prevents this.

DK

 

[wigi]

Hi Wigi

Regarding point 1, what happens if you double-click a cell normally (i.e. without any VBA code running)? For me (and also the OP it appears) double-clicking in the cell is the same as pressing F2 in the cell (edit mode). Setting the Cancel argument to true prevents this.

DK

Indeed, then I don't go in Edit mode...

 

[GTO]

Hi All,

Wigi,

I'm currently in 2000. My guess would be that you have 'Edit Directly in Cell' un-ticked under Tools|Options|(Edit Tab)

 

[wigi]

Wigi,

I'm currently in 2000. My guess would be that you have 'Edit Directly in Cell' un-ticked under Tools|Options|(Edit Tab)

Hello GTO,

I'm on Excel 2007, but yes you are right regarding that setting.

 

[dk]

Thanks GTO - forgot about that setting.

So it would make more sense to use Cancel = True as this allows for the possibility of both settings. If the user has "Enable editing directly in cells" unchecked then the line will have no effect. If it's checked then the line will prevent edit mode being activated.

DK

 

[GTO]

Thanks GTO - forgot about that setting.

So it would make more sense to use Cancel = True as this allows for the possibility of both settings. If the user has "Enable editing directly in cells" unchecked then the line will have no effect. If it's checked then the line will prevent edit mode being activated.

DK

Yes Sir, and at least AFAIK, utterly true, as the event would never be called.

 

[dk]

Yes Sir, and at least AFAIK, utterly true, as the event would never be called.

The event will get called but it will ignore the Cancel = True

 

[GTO]

Yikes! and of course you are perfectly correct. One too many adult beverages last night.

 

[Nalani]

Thanks to all.

wigi, your code is 'Nifty" as you stated above.

I took the advice from dk about the use of On Error Resume Next.
Commented out that statement first of all, just to see if there was an Error.
I then limited my Target to Column A.
It did throw an error if I DoubleClick in an empty cell.
Error was on this line:

 If Len(.VBComponents(sModule).Name) = 0 Then

Subscript out of range

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

This is what I came up with. Seems to work right, but not sure if I made the right adjustments.

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
 
    If Target.Column > 1 Then Exit Sub
    On Error GoTo ErrHandler
 
    sModule = Target.Value
 
    'Assume that the default action needs to be cancelled
    bCancelDefault = True
 
    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
 
    'If the procedure has opened the VBE then cancel the default action (starts edit mode in the cell)
    Cancel = bCancelDefault
    Exit Sub
 
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 "Please choose from the list in Column A", vbExclamation, "Error"
        Cancel = bCancelDefault
    End If
 
End Sub

 

[Nalani]

Sorry, forgot to mention that when the error occured, it would Add a module without any lines of code,

From what I can figure out, this was the intention of wigi's code.

But I didn't need the added module. So my adjustment took care of that.