Woodstock66
New Member
- Joined
- May 7, 2022
- Messages
- 6
- Office Version
- 2019
- Platform
- 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
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