List all subs into listbox

yinkajewole

Board Regular
Joined
Nov 23, 2018
Messages
209
how can i list all the subs in my project into a listbox and then run the subs from the listbox?
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,737
Hi

To get the names of all subs in your project you have to use the VBA Extensibility Model

You can learn it here:

http://www.cpearson.com/Excel/vbe.aspx

Check in the page:

Listing All Modules In A Project
Listing All Procedures In A Module

If I understand correctly after you have the names of the subs in the ListBox you want to execute them

To execute a sub given its name as a string you can use:

Code:
Application.Run "SomeSub"
 

yinkajewole

Board Regular
Joined
Nov 23, 2018
Messages
209
I've gone through it
(1) It can't list all the subs only (it always include functions, which I don't want)
(2) It can't list all the subs in the whole workbook (it only list from one module)
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,307
Here is link to code that could be modified
- it lists all procedures(all modules) in a new worksheet
- rename prodeures that you want so that the name always begins with the same text
- modify the code to exclude what you do not want or filter them out when adding to listbox
etc

https://excel.tips.net/T002715_Generating_a_List_of_Macros.html
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,698
I create a floating CommandBar to call my subs from. It doesn't require activating the Microsoft Visual Basic For Applications Extensibility library.

This version make the menu bar for the subs I specify.
Code:
Sub makeCommandBar()
    Dim newBar As CommandBar
    On Error Resume Next
        Application.CommandBars("MyFloatingMenu").Delete
    On Error GoTo 0
    Set newBar = Application.CommandBars.Add("MyFloatingMenu", Position:=msoBarFloating, temporary:=True)
    With newBar
        .Width = 135
        
        With .Controls.Add(Type:=msoControlButton, temporary:=True)
            .BeginGroup = True
            .Style = msoButtonCaption
            .Caption = "Get From Spotify"
            .Visible = True
            .OnAction = "GetFromSpotify"
            .Width = 130
        End With
        
        With .Controls.Add(Type:=msoControlButton, temporary:=True)
            .BeginGroup = False
            .Style = msoButtonCaption
            .Caption = "iTunes import"
            .Visible = True
            .OnAction = "ImportFromITunes"
            .Width = 130
        End With
        
        With .Controls.Add(Type:=msoControlButton, temporary:=True)
            .BeginGroup = True
            .Style = msoButtonCaption
            .Caption = "Spotify Rating"
            .Visible = True
            .OnAction = "ShowRating"
            .Width = 130
        End With
        
        With .Controls.Add(Type:=msoControlButton, temporary:=True)
            .BeginGroup = True
            .Style = msoButtonCaption
            .Caption = "TimeRemain formula"
            .Visible = True
            .OnAction = "TimeLeftFormula"
            .Width = 135
        End With
        
        With .Controls.Add(Type:=msoControlButton, temporary:=True)
            .BeginGroup = True
            .Style = msoButtonCaption
            .Caption = "Ordering Userform"
            .Visible = True
            .OnAction = "showOrderForm"
            .Width = 130
        End With
        
        .Top = 200
        .Left = 900
        .Visible = True
    End With
    
End Sub
Alternatly, one could read the modules and create a menu bar for all subs, except the excluded ones.

Code:
Sub ReadAndMakeCommandBar()
    Dim newBar As CommandBar
    Dim ArrNames As Variant, i As Long
    ArrNames = ListOfSubs
    For i = 1 To UBound(ArrNames)
        ArrNames(i) = Replace(ArrNames(i), "()", vbNullString)
        ArrNames(i) = Split(ArrNames(i), "Sub ")(1)
    Next i
    MsgBox RRayStr(ArrNames, vbCr)
    
    On Error Resume Next
        Application.CommandBars("MyFloatingMenu").Delete
    On Error GoTo 0
    Set newBar = Application.CommandBars.Add("MyFloatingMenu", Position:=msoBarFloating, temporary:=True)
    
    With newBar
        .Width = 135
        
        For i = 1 To UBound(ArrNames)
            Select Case LCase(ArrNames(i))
            Case "readandmakecommandbar", "makecommandbar"
                Rem do nothing
            Case Else
                Rem make a button
                With .Controls.Add(Type:=msoControlButton, temporary:=True)
                    .Style = msoButtonCaption
                    .Caption = ArrNames(i)
                    .Visible = True
                    .OnAction = ArrNames(i)
                    .Width = 130
                End With
            End Select
        Next i
        
        .Top = 200
        .Left = 900
        .Visible = True
    End With

End Sub

Function ListOfSubs() As Variant
    Dim oneVBComp As VBComponent
    Dim oneModule As CodeModule
    Dim xStr As String, i As Long
    Dim arrSubNames() As String, SubCount As Long
    ReDim arrSubNames(1 To 1)
    With ThisWorkbook.VBProject
        For Each oneVBComp In .VBComponents
            With oneVBComp
                If .Type = vbext_ct_StdModule Then
                    With .CodeModule
                        For i = 1 To .CountOfLines
                            If .Lines(i, 1) Like "*Sub *()" Then
                                SubCount = SubCount + 1
                                If UBound(arrSubNames) < SubCount Then ReDim Preserve arrSubNames(1 To 2 * SubCount)
                                arrSubNames(SubCount) = .Lines(i, 1)
                            End If
                        Next i
                    End With
                End If
            End With
        Next oneVBComp
    End With
    If SubCount > 0 Then
        ReDim Preserve arrSubNames(1 To SubCount)
    Else
        ReDim arrSubNames(0 To 0)
    End If
    ListOfSubs = arrSubNames
End Function
If, in the function ListOfSubs, you change the definition of the VBComponent and CodeModule to Object, you might not need to activate that reference.
(The explicit listing method of MakeCommandBar does not need that reference.)
 
Last edited:

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,092
I would think the easy way would be to load all your Sub's into a ListBox like this.
And then use this script.

Code:
Private Sub CommandButton1_Click()
'Modified  8/14/2019  9:17:49 PM  EDT
    With ListBox1
        .AddItem "One"
        .AddItem "Two"
        .AddItem "Three"
    End With
End Sub
Private Sub ListBox1_Click()
Application.Run ListBox1.Value
End Sub
Code:
Sub One()
MsgBox "One"
End Sub
Sub Two()
MsgBox "Two"
End Sub
Sub Three()
MsgBox "Three"
End Sub
 

Forum statistics

Threads
1,078,544
Messages
5,341,086
Members
399,417
Latest member
Andrea Elyas

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top