Create clickable index to edit macros

I have workbooks which contain no data, containing only my "go to" standard macros to suit different requirements.
Finding the macro to edit or run by scrolling through the standard macro list is painful when there is a long list of similarly named macros.

To make life easier I want to create an index on the first sheet of each workbook comprising:
- a list of all the macros in the workbook (=columnA) [help NOT required here]
- a description of what each macro does (=column B [help NOT required here]
- somewhere to click in the index to RUN the macro (=column C) [help NOT required here]
- somewhere to click in the index to EDIT the macro (= column D) [help requested here only]

To run a macro is straightforward. Attach this code to a box etc

Please Login or Register  to view this content.

What I am looking for is an equivalent

Please Login or Register  to view this content.

There does not appear to be an obvious one liner, but is there a way to achieve this?
thanks

Hi Kevin,

I was partially successful in doing what you want. I was unable to get UserForms, Sheet Modules, or the ThisWorkbook module to cooperate. Ordinary code modules and Class modules seem to work ok. Column 'D' should contain either the Macro Name (e.g. 'Macro1') or the combination Module and Macro name (e.g. 'Module1.Macro1). If the 'Module name' is omitted, the first Macro that has a matching name (in any module) will be the target.

Without the following VBA Reference,a compile error will occur
Tools > Reference > Microsoft Visual Basic For Applications Extensibility 5.3 (or equivalent)

See the attached sample file that contains the following code in ordinary code module ModVBEGoTo:

Please Login or Register  to view this content.

If I find a solution to the parts that do not work, I will post an update. The line in red above, and duplicated below is the line that seems to cause the problem. It refuses to put the focus on a Sheet module or the ThisWorkbook module. On a UserForm module, the focus moves to the Userform, and not the UserForm Code.

Lewis

Thanks Lewis. This is amazing.
I had absolutely no idea that my simple sounding request would require such complex coding.
I have tried it out and it works just like you say.
I now look forward to studying the code and getting to grips with how it all links together.
Kevin

Thank you @shg.
Your answer made me try a simple bit of code and it works

normal module

Please Login or Register  to view this content.

and for the other obvious bunch:

sheet module

Please Login or Register  to view this content.

another workbook

Please Login or Register  to view this content.

sheet module in another workbook

Please Login or Register  to view this content.

I can get this to work with everything that is visible in the standard macro list box
Now to find a way to get at userforms, workbook modules etc

One Solution for quick index

On Sheet1
Procedure Names in columnA
Procedure Module in columnB (As displayed in vb window ie sheet index not sheet name)

Double click on procedure name and the code below(embedded in Sheet1) takes you to vba edit for that macro

Please Login or Register  to view this content.

Hi Kevin,

Nice job in post #6. Your simple code looks great. Thanks for the rep points.

I was able to upgrade my code after a lot of help from others to be able to access UserForms and Sheet Module Code in the attached file.

Sheet Modules provided a unique challenge because Sheets can be referenced using either 'Sheet Name' or 'Code Name'. Many advanced users prefer Code Names, because Sheet Names are easily changed by the User. I prefer 'Sheet Names' because they are more descriptive, and are more stable if Sheets are added and/or deleted.

See the following reference: http://www.wiseowl.co.uk/blog/s112/m...e-codename.htm

Lewis

Thanks again Lewis
- that will fill some gaps in my list and mean that my index will now also include UserForms which is great - they are rather cumbersome beasts and it will be good to get a proper handle on them.
- I will study the code in some detail this week and I have a feeling that I will have quite a few questions - it is totally beyond what I have ever attempted.
Kevin

Kevin,

Don't underestimate your ability. You seem to have an excellent handle on VBA. Most items are a lot of simple things that are put together to make it appear complicated. The following may help you:

Debugger Secrets:
a. Press 'F8' to single step (goes into subroutines and functions).
b. Press SHIFT 'F8' to single step OVER subroutines and functions.
c. Press CTRL 'F8' to stop at the line where the cursor is.
d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
output to the IMMEDIATE WINDOW.
f. Select View > Locals to see all variables while debugging.
g. To automatically set a BREAKPOINT at a certain location put in the line:
'Debug.Assert False'
h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
if i >= 20 and xTV20 > 99.56 then
Debug.Assert False
endif
i. A variable value will be displayed by putting the cursor over the variable name.

To manually set a breakpoint, see http://www.wiseowl.co.uk/blog/s196/breakpoints.htm

Lewis