Question on myMacroLstToSh()

Woodstock66

New Member
Joined
May 7, 2022
Messages
6
Office Version
  1. 2019
Platform
  1. Windows
Hi
Some time ago I found this Excel macro on MrExcel: myMacroLstToSh().
It lists all projects and macro's you have in your Excel workbook,
It is rather old, but works fine. :-)
I was wondering whether it is possible to add a hyperlink of some kind that 'brings' you to the macro in the project instead of running it

Thanks in advance.

Sub myMacroLstToSh()
'You will need to create a reference to MS VBA Extensibilty 5.3
'(in VBE go to Tools>References) to work this code, it was set in this version.
'Standard module code, like: Module1.
Dim VBCodeMod As CodeModule
Dim StartLine&, nRow&

'Row to add list labels in [Top of List Row]!
nRow = 5

'My list's column labels.
Sheets("Sheet2").Select
ActiveSheet.Cells(nRow, 1) = "Module's"
ActiveSheet.Cells(nRow, 1).Font.Bold = True
ActiveSheet.Cells(nRow, 1).HorizontalAlignment = xlCenter

ActiveSheet.Cells(nRow, 2) = "Macro's"
ActiveSheet.Cells(nRow, 2).Font.Bold = True
ActiveSheet.Cells(nRow, 2).HorizontalAlignment = xlCenter

'Get Modules.
For Each projMod In Application.VBE.ActiveVBProject.VBComponents
Set VBCodeMod = Application.ActiveWorkbook.VBProject.VBComponents(projMod.Name).CodeModule

With VBCodeMod
'Index the module's name!
StartLine = .CountOfDeclarationLines + 1

Do Until StartLine >= .CountOfLines
'Increase Row for each listing!
nRow = nRow + 1
'Load cell with name!
Sheets("Sheet2").Cells(nRow, 1) = projMod.Name
Sheets("Sheet2").Cells(nRow, 2) = .ProcOfLine(StartLine, vbext_pk_Proc)

StartLine = StartLine + .ProcCountLines(.ProcOfLine(StartLine, _
vbext_pk_Proc), vbext_pk_Proc)
Loop
End With
Next projMod
End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
At first I wondered if there would be a way to replicate ctrl+F, pass the search term and replicate the dialog button push but then figured it would never work because code would be running. Tested ctrl+f while stepping through a sub and guess what? Dialog opened and I could find the search term in code.
Then, apparently it's possible to run code from a hyperlink so maybe make use of its descriptive text or something (the name of the sub), and pass that to a sub that scans through the project modules and opens that procedure. I have very little experience with the vb editor object model so I couldn't even begin to suggest how to do that.
HTH
 
Upvote 0
At first I wondered if there would be a way to replicate ctrl+F, pass the search term and replicate the dialog button push but then figured it would never work because code would be running. Tested ctrl+f while stepping through a sub and guess what? Dialog opened and I could find the search term in code.
Then, apparently it's possible to run code from a hyperlink so maybe make use of its descriptive text or something (the name of the sub), and pass that to a sub that scans through the project modules and opens that procedure. I have very little experience with the vb editor object model so I couldn't even begin to suggest how to do that.
HTH
Hi Micron
Thanks for sharing your thoughts. It's a nice idea, but unfortunately I'm also not that experienced.
"Google" did not give me a solution so far, that is why I try here.
Hopefully others have some ideas as well, or may be it is just not possible.
 
Upvote 0
The vb editor (or perhaps more accurately, a vb project) is another world of objects that I'm not real familiar with. I have researched and have seen that there is a VBIDE object model with things like CodeModule and CodePane objects, but when I browse the object model documentation it just makes my head spin! I gathered from your post that maybe you knew more about that than I did. I find the whole object model somewhat daunting. If you want to go down that rabbit hole into Wonderland you might start with

Maybe you'll meet Alice or the Cheshire Cat in there!
 
Upvote 0
Hi
I found a solution in this post: Macro to call Visual Basic Editor?

Sub GoToMacro(macroName As String)
Application.Goto Reference:=macroName
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Value <> "" And ActiveCell.Column = 2 Then
Call GoToMacro(ActiveCell.Value)
End If
End Sub
 
Upvote 0
Solution
@MARK858; thanks - I'll bookmark that. It looks like a much better resource than the cryptic M$ documentation.
 
Upvote 0

Forum statistics

Threads
1,223,275
Messages
6,171,127
Members
452,381
Latest member
Nova88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top