How to create a macro that will open a userform from an Add-in without opening the workbook

Question

I developed a userform that queries the form's workbook for information. I want this userform to be available in other workbooks so users can lookup information in the form's workbook while working in their own workbooks.

Originally I tried using this on opening the workbook:

Application.visible = False
Userform.show vbmodeless

However, this will hide all other workbooks in excel which will confuse the user.

I then tried saving the userform workbook as an excel add-in and created a macro in the ribbon that will launch the form via the show vbmodeless function, but this pulls up the userform's workbook as well.

I am new to VBA; is there anyway to pull this off where a user can be working in their workbook and pull up my userform without opening the userform's workbook. The userform does not interact nor need to interact at all with the individual's personal workbooks, just the workbook from which the form originates. I would prefer this to work from the ribbon, but if it is just from opening the userform workbook that is ok too.

Thanks!

Answer 1

I think your issue is possibly with the way you have added it to the ribbon. I have an addin I create for my team of "useful Macro's", which has several that call userforms, and I have no issues with the addin workbook being displayed. Not sure if there are other methods, but here is how I did it.
In the "This Workbook" Workbook_Open section I have the following lines of code

Call enableAddin
call CreateMMMacroMenu

The enableAddin is an autoinstall routine which you can use or not, this is the code for that:

'---------------------------------------------------------------------------------------
' Procedure : enableAddIn
' Purpose   : Auto installed for the add-in
' Version   : 19/11/2014 : Mark Moore - Initial Version
'---------------------------------------------------------------------------------------
Sub enableAddIn()
Dim A As AddIn
Dim Listed As Boolean
On Error Resume Next
    Listed = False
    For Each A In Application.AddIns
        If A.Name = ThisWorkbook.Name Then
'            A.Installed = True
            Listed = True
            Exit Sub
        End If
    Next
    If Listed = False Then
        If MsgBox("This will install the 'Usefull Macros' Addin, do you wish to continue?" & _
         vbCrLf & vbCrLf & "NB: This file should be in the permanent location you will leave it in, " & _
         "as the addin will be accessed from its current location.  If you wish to put this file " & _
         "elsewhere, before installing it, select 'No' anf move the file to its permant location " & _
         "before opening it again", vbYesNo, "Install 'Usefull Macros' Addin?") = vbYes Then
            Application.Workbooks.Add
            AddIns.Add(ThisWorkbook.FullName, True) _
            .Installed = True
        Else
            MsgBox "Install Cancelled"
        End If
    End If
On Error GoTo 0
End Sub

The CreateMMMacroMenu is the one that actually creates the menu on the ribbon, and this is my version, but hopefully you can see how it works, and in particular the button 6 is a popup macro and the onaction is "FindandCopy" and this is one line that simply does frmFindAndCopy.show

'---------------------------------------------------------------------------------------
' Procedure : CreateMMMacroMenu
' Purpose   : Creates the Useful Macros Commnad bar and associated buttons
' Version   : 18/11/2014 : Mark Moore - Initial Version
'---------------------------------------------------------------------------------------
Sub CreateMMMacroMenu()
Dim myCB As CommandBar
Dim myCPup1 As CommandBarPopup

    ' Delete the CommandBar if it exists already, will error if it doesnt exist, so switch erro handling off
    On Error Resume Next
    Application.CommandBars("MMMacroMenu").Delete

    'Switch error handling back on
    On Error GoTo CreateMMMacroMenu_Error

    ' Create a new CommandBar
    Set myCB = CommandBars.Add(Name:="MMMacroMenu", Position:=msoBarFloating)

    ' Add popup menu 1 to this bar - this is a menu that folds out
    Set myCPup1 = myCB.Controls.Add(Type:=msoControlPopup)
    With myCPup1
        .Caption = "Useful Macro's"
    End With

    ' Add button 1 to popup menu 1 - "Quick and Dirty Export" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Export Delimited text File (Quick and dirty)"
     .Style = msoButtonIconAndCaption   'Make button show caption text and icon
     .OnAction = "ExportCurrentSheetAll"    'Macro to be called
     .TooltipText = "Exports the entire userd range of current tab to pipe delimited text file, default name and location"
     .FaceId = 1713
    End With

    ' Add button 2 to popup menu 1 - "Export with options" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Export Delimited text File (With options)"
     .Style = msoButtonIconAndCaption   'Make button show caption text and icon
     .OnAction = "ExporttoFileWithoptions"    ''Macro to be called
     .TooltipText = "Will present a user form to allow customisation of the export"
     .FaceId = 1713
    End With

    ' Add button 3 to popup menu 1 - "Create SQL IN" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Create SQL 'IN' Statement from selected cells"
     .Style = msoButtonIconAndCaption   'Make button show caption text
     .OnAction = "ShowSQLCreateForm"    'Macro to be called
     .TooltipText = "Will present a user form to allow creation of a SQL 'IN' statement from the selected cells"
     .FaceId = 528
    End With


    'Add button 4 to popup menu 1 - "Show Used Range" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Show what Excel thinks is the Used range for current sheet"
     .Style = msoButtonIconAndCaption   'Make button show caption text and icon
     .OnAction = "Whats_The_UsedRange"    'Macro to be called
     .TooltipText = "Will show the 'UsedRange' of the active sheet"
     .FaceId = 8
    End With

    'Add button 5 to popup menu 1 - "Used Range report" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Produce 'Used range report' for active workbook"
     .Style = msoButtonIconAndCaption   'Make button show caption text and icon
     .OnAction = "UsedRangeReport"    'Macro to be called
     .TooltipText = "Will produce a text file report of the used range statistics for all of the sheets in the active workbook"
     .FaceId = 852
    End With

    'Add button 6 to popup menu 1 - "Find and copy" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Find and copy rows of data to a new sheet"
     .Style = msoButtonIconAndCaption   'Make button show caption text and icon
     .OnAction = "FindandCopy"    'Macro to be called
     .TooltipText = "Will present a dialogue to allow the user to search for a string in the active sheet and any rows with matching values are copied to a new sheet"
     .FaceId = 1714
    End With

    'Add button 7 to popup menu 1 - "Unhide all sheets" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Unhide all Sheets in active workbook"
     .Style = msoButtonIconAndCaption   'Make button show caption text and icon
     .OnAction = "UnhideAllSheets"    'Macro to be called
     .TooltipText = "Will simply attempt to unhide all sheets in the active workbook"
     .FaceId = 2125
    End With

    'Add button 8 to popup menu 1 - "ToggleWorksheet events" macro
    Set myCP1Btn1 = myCPup1.Controls.Add(Type:=msoControlButton)
    With myCP1Btn1
     .Caption = "Toggle Calcuation and worksheet events on and off"
     .Style = msoButtonIconAndCaption   'Make button show caption text and icon
     .OnAction = "UnhideAllSheets"    'Macro to be called
     .TooltipText = "Used for debugging of if code has crashed without turning these featuires back on - Dont touch this if you don't know what you are doing"
     .FaceId = 2933
    End With

    ' Show the command bar
    myCB.Visible = True

CreateMMMacroMenu_Exit:
    On Error GoTo 0
    Exit Sub

CreateMMMacroMenu_Error:
    If Application.ScreenUpdating = False Then Application.ScreenUpdating = True
    Select Case Err.Number
    Case Else
        MsgBox "An unexpected error has occured, please contact CSC DM Design with the below error details." & _
            vbCrLf & "Module = UsefulGenericCode" & _
            vbCrLf & "Procedure = CreateMMMacroMenu" & _
            vbCrLf & "Line = " & Erl & _
            vbCrLf & "Error Code = " & Str$(Err.Number) & _
            vbCrLf & "Error Text = " & Err.Description & _
            vbCrLf & vbCrLf & "", vbCritical, _
            Msgboxtitle
    End Select
    Resume CreateMMMacroMenu_Exit

End Sub

And finally also in the "This Workbook" Workbook_BeforeClose event, I have the following to remove the menu bars when excel closes

Call DeleteMMMacroMenu

'---------------------------------------------------------------------------------------
' Procedure : DeleteMMMacroMenu
' Purpose   : Deletes the custom "Useful macros toolbar" - called by the workbookclose event
' Version   : 18/11/2014 : Mark Moore - Initial Version
'---------------------------------------------------------------------------------------
Sub DeleteMMMacroMenu()
    On Error Resume Next
    CommandBars.FindControl(Tag:="MMMacroMenu").Delete
    CommandBars("MMMacroMenu").Delete
End Sub