如何创建一个宏，可以从加载项中打开一个用户窗体，而无需打开工作簿 [英] How to create a macro that will open a userform from an Add-in without opening the workbook

问题描述

我开发了一个用户表单来查询表单的工作簿以获取信息。我希望此用户表在其他工作簿中可用，以便用户可以在自己的工作簿中查找表单工作簿中的信息。

最初我尝试使用这个打开工作簿：

Application.visible = False

Userform.show vbmodeless

但是，这将隐藏excel中的所有其他工作簿，这将混淆用户。

然后，我尝试将用户形式的工作簿保存为excel加载项，并在功能区中创建一个宏，该宏将通过show vbmodeless函数启动表单，但这也会拉出userform的工作簿。我是VBA的新人，无论如何，无论如何，用户可以在其工作簿中工作，并拉开我的用户窗体，而不打开用户窗体的工作簿。用户形式不会与个人的个人工作簿完全交互，也不需要与表单发起的工作簿进行交互。我更喜欢这个工作从功能区，但如果它只是从打开用户形式的工作簿也可以。

谢谢！

解决方案

我想你的问题可能在你把它添加到功能区的方式。我有一个我为我的团队创建的有用的宏，其中有几个调用userforms，我没有显示的插件工作簿的问题。不知道是否有其他方法，但这里是我如何做的。

在本工作簿的工作簿_开头部分我有以下代码行

调用enableAddin
调用CreateMMMacroMenu

enableAddin是一个可以使用或不使用的自动安装例程，这是代码：

 '------ -------------------------------------------------- ------------------------------- 
'过程：enableAddIn 
'用途：自动安装加载项
'版本：19/11/2014：Mark Moore  - 初始版本
'------------------------ -------------------------------------------------- ------------- 
 Sub enableAddIn（）
 Dim A As AddIn 
 Dim Listed As Boolean 
 On Error Resume Next 
 Listed = False 
对于每个A在Application.AddIns 
如果A.Name = ThisWorkbook.Name然后
'A.Installed = True 
 Listed = True 
退出Sub 
 End If 
下一个
如果Listed = False然后
如果MsgBox（这将安装'Usefull Macros'Addin，你想继续吗？ _ 
 vbCrLf& vbCrLf& 注意：这个文件应该在你将要离开的永久的位置，& _ 
，因为插件将从当前位置访问，如果你想把这个文件& _ 
其他地方，在安装之前，选择否将文件移动到其永久位置& _ 
再打开之前，vbYesNo，安装'usefull Macros'Addin？）= vbYes然后
 Application.Workbooks.Add 
 AddIns.Add（ThisWorkbook.FullName，True） _ 
 .Installed = True 
 Else 
 MsgBoxInstall Cancelled
 End If 
 End If 
 On Error GoTo 0 
 End Sub 
  

CreateMMMacroMenu实际上是在功能区上创建菜单，这是我的版本，但希望你可以看到它是如何工作的，特别是按钮6是一个弹出宏，而且这个操作是FindandCopy，这是一个简单的 frmFindAndCopy.show

 '-------------- -------------------------------------------------- ----------------------- 
'过程：CreateMMMacroMenu 
'目的：创建有用的宏Commnad栏和关联的按钮
'版本：18/11/2014：马克·摩尔 - 初始版本
'------------------------------------------ --------------------------------------------- 
 Sub CreateMMMacroMenu（）
 Dim myCB As CommandBar 
 Dim myCPup1 As CommandBarPopup 
 
'删除CommandBar（如果它已经存在），如果它不存在就会出错，所以切换erro处理
 On Error Resume Next 
 Application.CommandBars（MMMacroMenu）。删除
 
'在
上切换错误处理返回错误GoTo CreateMMMacroMenu_Error 
 
 '创建一个新的CommandBar 
设置myCB = CommandBars.Add（名称：=MMMacroMenu，位置：= msoBarFloating）
 
'将弹出菜单1添加到此栏 - 这是一个菜单折叠
设置myCPup1 = myCB.Controls.Add（Type：= msoControlPopup）
与myCPup1 
 .Caption =有用的宏
结束
 
'添加按钮1到弹出菜单1  - 快速和脏的导出宏
设置myCP1Btn1 = myCPup1.Controls.Add（Type：= msoC ontrolButton）
与myCP1Btn1 
 .Caption =导出分隔的文本文件（快速和脏）
 .Style = msoButtonIconAndCaption'使按钮显示字幕文本和图标
 .OnAction = ExportCurrentSheetAll'宏被调用
 .TooltipText =将当前选项卡的整个userd范围导出到管道分隔的文本文件，默认名称和位置
 .FaceId = 1713 
 End with 
 
'添加按钮2到弹出菜单1  - 导出与选项宏
设置myCP1Btn1 = myCPup1.Controls.Add（类型：= msoControlButton）
与myCP1Btn1 
。 Caption =导出分隔的文本文件（带选项）
 .Style = msoButtonIconAndCaption'使按钮显示字幕文本和图标
 .OnAction =ExporttoFileWithoptions'宏被调用
 .TooltipText =将提交用户表单以允许自定义导出
 .FaceId = 1713 
结束
 
'添加按钮3 t o弹出菜单1  - 创建SQL IN宏
设置myCP1Btn1 = myCPup1.Controls.Add（类型：= msoControlButton）
使用myCP1Btn1 
 .Caption =创建SQL'IN'选定的单元格
 .Style = msoButtonIconAndCaption'使按钮显示标题文本
 .OnAction =ShowSQLCreateForm'宏被调用
 .TooltipText =将呈现用户窗体以允许创建来自所选单元格的SQL'IN'语句
 .FaceId = 528 
结束
 
 
'添加按钮4弹出菜单1  - 显示使用范围宏
设置myCP1Btn1 = myCPup1.Controls.Add（类型：= msoControlButton）
使用myCP1Btn1 
 .Caption =显示Excel认为是当前工作表的使用范围
。 Style = msoButtonIconAndCaption'Make button显示字幕文本和图标
 .OnAction =Whats_The_UsedRange'要调用的宏
 .TooltipText =将显示活动的 t
 .FaceId = 8 
结束
 
'添加按钮5弹出菜单1  - 使用范围报告宏
设置myCP1Btn1 = myCPup1.Controls。添加（类型：= msoControlButton）
使用myCP1Btn1 
 .Caption =生成活动工作簿的使用范围报告
 .Style = msoButtonIconAndCaption'使按钮显示字幕文本和图标
.OnAction =UsedRangeReport'被调用的宏
 .TooltipText =将生成活动工作簿中所有工作表的使用范围统计信息的文本文件报告
 .FaceId = 852 
结束
 
'添加按钮6弹出菜单1  - 查找和复制宏
设置myCP1Btn1 = myCPup1.Controls.Add（Type：= msoControlButton）
使用myCP1Btn1 
 .Caption =查找并复制数据行到新的工作表
 .Style = msoButtonIconAndCaption'使按钮显示字幕文本和图标
 .OnAction =FindandCopy'宏到被称为
 .TooltipText =将出现一个对话框，允许用户在活动工作表中搜索一个字符串，并将具有匹配值的任何行复制到新的工作表
 .FaceId = 1714 
 End with 
 
'添加按钮7到弹出菜单1  - 取消隐藏所有工作表宏
设置myCP1Btn1 = myCPup1.Controls.Add（Type：= msoControlButton）
使用myCP1Btn1 
。 Caption =取消隐藏活动工作簿中的所有表
 .Style = msoButtonIconAndCaption'使按钮显示字幕文本和图标
 .OnAction =UnhideAllSheets宏被调用
 .TooltipText =将只需尝试取消隐藏活动工作簿中的所有工作表
 .FaceId = 2125 
结束
 
'添加按钮8到弹出菜单1  - ToggleWorksheet事件宏
设置myCP1Btn1 = myCPup1.Controls.Add（类型：= msoControlButton）
使用myCP1Btn1 
 .Caption =切换计算和工作表事件开启和关闭
 .Style = msoButtonIco nAndCaption'Make按钮显示字幕文本和图标
 .OnAction =UnhideAllSheets宏被调用
 .TooltipText =用于调试代码是否崩溃而不转动这些功能 - 不要触摸这个如果你不知道你在做什么
 .FaceId = 2933 
结束
 
'显示命令栏
 myCB.Visible = True 
 
 CreateMMMacroMenu_Exit：
错误GoTo 0 
退出Sub 
 
 CreateMMMacroMenu_Error：
如果Application.ScreenUpdating = False然后Application.ScreenUpdating = True 
选择案例Err.Number 
案例Else 
 MsgBox发生意外错误，请联系CSC DM Design，并提供以下错误详细信息。 &安培; _ 
 vbCrLf& Module = UsefulGenericCode& _ 
 vbCrLf& Procedure = CreateMMMacroMenu& _ 
 vbCrLf& Line =& Erl& _ 
 vbCrLf& Error Code =& Str $（Err.Number）& _ 
 vbCrLf& Error Text =&错误描述& _ 
 vbCrLf& vbCrLf& ，vbCritical，_ 
 Msgboxtitle 
结束选择
恢复CreateMMMacroMenu_Exit 
 
 End Sub 
  

最后还要在This WorkbookWorkbook_BeforeClose事件中，当excel关闭时，我有以下功能来删除菜单栏。

  Call DeleteMMMacroMenu 
 
'----------------------------- -------------------------------------------------- -------- 
'过程：DeleteMMMacroMenu 
'目的：删除自定义的有用的宏工具栏 - 由工作簿关闭事件调用
'版本：18/11/2014：马克·摩尔 - 初始版本
'---------------------------------------- ----------------------------------------------- 
 Sub DeleteMMMacroMenu（）
 On Error Resume Next 
 CommandBars.FindControl（Tag：=MMMacroMenu）。删除
 CommandBars（MMMacroMenu）。删除
 End Sub 
  

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!

解决方案

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

这篇关于如何创建一个宏，可以从加载项中打开一个用户窗体，而无需打开工作簿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！