CODEWITHSUNDEEP
excelvbauserform
user11788532
user11788532

Reputation: 25

Activate USERFORM from excel add-ins

I have written VBA with 3 modules that work perfectly and the userform works as well when I run it from Developer - VBA window. I need to add the userform to my add-in to activate when I need it. I added the 4th module to install the macro

Sub Add_MainframeScrape_Menu()
    Dim cbWSMenuBar As CommandBar
    Dim muInbound As CommandBarControl
    Dim iHelpIndex As Integer

    Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
    iHelpIndex = cbWSMenuBar.Controls("Help").Index
    Set muInbound = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpIndex)

    With muInbound
        .Caption = "EDIMACROS"
        With .Controls.Add '(Type:=msoControlPopup)
            .Caption = "EDIMACROS"
            .OnAction = "EDI_REPORTS"
       End With
    End With
End Sub

And I added the below code to the form itself to start when I click on add-ins 

Public Sub EDI_MACROS_Initialize()
    Me.Show
End Sub

But it's not working please help.

Upvotes: 0

Views: 953

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

The OnAction should be the name of a Sub (in a regular module) which displays the form, not the name of the form

Sub Add_MainframeScrape_Menu()
    Dim cbWSMenuBar As CommandBar
    Dim muInbound As CommandBarControl
    Dim iHelpIndex As Integer

    Set cbWSMenuBar = Application.CommandBars("Worksheet Menu Bar")
    iHelpIndex = cbWSMenuBar.Controls("Help").Index
    Set muInbound = cbWSMenuBar.Controls.Add(Type:=msoControlPopup, _
                                             Before:=iHelpIndex)

    With muInbound
        .Caption = "EDIMACROS"
        With .Controls.Add '(Type:=msoControlPopup)
            .Caption = "EDIMACROS"
            .OnAction = "Show_EDI_MACROS"
       End With
    End With
End Sub


Public Sub Show_EDI_MACROS()
    EDI_REPORTS.Show 'assumes your form is named "EDI_REPORTS"
End Sub

Upvotes: 1

Related Questions