How to make a list of macros ?

Jchicoine

New Member
Joined
Apr 29, 2002
Messages
22
Hi ! I've seen a previous answer to that question but I'm not able to do it ! I have many macros in my spreadsheet and I would like to choose them from a list. Is there a easy way to do it ?

Thanks a lot !
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Jchicoine

New Member
Joined
Apr 29, 2002
Messages
22
Thanks ! But I would like a dropdown list from my spreadsheet !

Thanks again !
 

Jack in the UK

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
Ok i dont know a VBA code to list all SUBs and Private, Funcytions and so on in the VBE of Excel but

I have billions of codes in sheets and have about 15 addin i have written WHAT A PAIN, so i have now all the subs in and have data sheet that lists the VBA script and have target codes to run when i need them, bjut its a pain to many functions and its slow up excels seed so i have most on subs to stop this

I add to the list each new goodie i add,

HTH
Jack [all in all as Al says same as suggested really]
 

NateO

Legend
Joined
Feb 17, 2002
Messages
9,700

ADVERTISEMENT

Howdy Jchicoine, here's one way of stacking a combobox on your first indexed sheet. I would run it it through WrkWRefs which will call the second procedure to ensure that your MS Visual Basic for Applications Extensibility library is indeed installed.<pre>
Sub WrkWRefs()
Dim n As Integer, p As String
For n = 1 To ThisWorkbook.VBProject.References.Count
p = ActiveWorkbook.VBProject.References.Item(n).Description
If InStr(p, "Visual Basic for Applications Extensibility") Then _
GoTo 1
Next n
ThisWorkbook.VBProject.References.AddFromGuid _
"{0002E157-0000-0000-C000-000000000046}", 1, 0
1: Call ProcLst
End Sub

Private Sub ProcLst()
Dim i As Integer, cnt As Integer, f As Integer, MyProc As String
Dim z As String
Application.EnableEvents = False
Sheets(1).ComboBox1.clear
For Each VBComponent In ThisWorkbook.VBProject.VBComponents
With VBComponent.CodeModule
For i = 1 To .CountOfLines
If .ProcOfLine(i, vbext_pk_Proc) > "" Then
MyProc = .ProcOfLine(i, vbext_pk_Proc)
cnt = 0
For f = 0 To Sheets(1).ComboBox1.ListCount - 1
z = Sheets(1).ComboBox1.List(f)
If z = MyProc Then cnt = cnt + 1
Next f
If cnt = 0 Then
If MyProc<> "WrkWRefs" And MyProc<> "ProcLst" _
And MyProc<> "ComboBox1_Change" Then _
Sheets(1).ComboBox1.AddItem MyProc
End If
End If
Next i
End With
Next VBComponent
Sheets(1).ComboBox1.Value = Sheets(1).ComboBox1.List(0)
Application.EnableEvents = True
End Sub</pre>

This may require some customization, and add or remove procedures to to the three I excluded from the list.

Now, in your sheets(1) worksheet code module, you'll want something like the following to initiate the macro you select from your drop down list:<pre>
Private Sub ComboBox1_Change()
Application.EnableEvents = False
Application.Run Me.ComboBox1.Value
Application.EnableEvents = True
End Sub</pre>

This worked well for me in Xl 2000 under some limited testing. Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>
This message was edited by nateo on 2002-10-09 12:47
 

stevebausch

Well-known Member
Joined
May 11, 2002
Messages
810
Place all the "macros" in one module. DO NOT use "Option Private Module".

In all other modules, insert the "Option Private Module" statement at the beginning of module.

Alt-8 will take you to the list.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,402
Messages
5,601,475
Members
414,452
Latest member
Dannysamworth

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
Top