从一个文件调用另一个UserForm [英] Call from one file a UserForm in another

问题描述

编写VBA代码，将一个Excel文件的UserForm调用到名为 john 的文件夹中的所有其他Excel文件，并将主Excel （由以下代码和用户表单组成）存在于不同的位置：

  Private Sub Workbook_OnClick（）
 Dim mypath As String 
 Dim file As String 
 Dim wb As Workbook 
 Dim pat As String 
 Application.ScreenUpdating = False 
 ChDriveC：
 ChDirC：\Users\Administrator\Desktop\John
'john是一个由excel文件组成的文件夹
 mypath = Range（值
'mypath具有与chDir 
文件= Dir（mypath&\&* .xlsx）相同的值
尽管文件& ; 
设置wb = Application.Workbooks.Open（文件）
如果不是IsEmpty（wb）然后
 Application.Visible = False 
 userform1.Show 
 End If 
 wb.Close 
 file = Dir（）
循环
结束子
  

代码是在主Excel文件上拉取UserForm，而不是在 john 文件夹中存在的Excel文件。

解决方案

包含您要显示的UserForm的工作簿也应该有一个显示窗体的过程。您将需要调用此过程来显示用户窗体。它可以是一个函数或一个子，我喜欢函数，因为你可以返回成功/失败的错误处理。

在UserForm工作簿中，你将添加一个类似于Module1（或任何模块，但您需要稍后参考）的过程：

 公共功能ShowTheForm可选Modal As Boolean = False）
'在另一个工作簿中显示用户窗体的API 
 
错误恢复下一步
 UserForm1.Show IIF（Modal，vbModal，vbModeless ）
 ShowTheForm =（Err.Number = 0）
结束函数
  

然后在试图将此表单打开的工作簿中，您将需要调用 ShowTheForm 程序，如下所示：

  Do While file<> 
设置wb = Application.Workbooks.Open（文件）
如果不是IsEmpty（wb）然后
 Application.Visible = False 
 Application.Run（'& wb.Name&'！Module1.ShowTheForm）
 End If 
 wb.Close 
 file = Dir（）
循环
  

由于您已将 ShowTheForm 作为具有返回值的函数，您可以捕获错误，例如：

 如果不是Application.Run（'& wb.Name&'！Module1.ShowTheForm ）然后
 MsgBox无法显示...
退出子
结束如果
  

根据这里提供的一般逻辑修改/增强：

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-you-open-userform-in-a nother-workbook / e97b2c06-2a79-4cef-89bc-4f67b0f3c03a？db = 5& auth = 1

注意 p>

我认为 IsEmpty 不是对工作簿对象的适当测试，您可能需要查看。我不知道你在做什么，但我几乎肯定不会做你认为这样做。

Write VBA code that calls a UserForm of one Excel file to all the other Excel files present in a folder called john and the master Excel (consists of the following code and the user form) is present in a different location:

 Private Sub Workbook_OnClick()
    Dim mypath As String
    Dim file As String
    Dim wb As Workbook
    Dim pat As String
    Application.ScreenUpdating = False
    ChDrive "C:"
    ChDir "C:\Users\Administrator\Desktop\John"
    'john is a folder that consists of the excel files 
    mypath = Range("B1").Value
    'mypath has the same value as chDir
    file = Dir(mypath & "\" & "*.xlsx")
    Do While file <> ""
        Set wb = Application.Workbooks.Open(file)
        If Not IsEmpty(wb) Then
            Application.Visible = False
            userform1.Show
        End If
        wb.Close
        file = Dir()
    Loop
End Sub

The code is pulling the UserForm on the master Excel file instead of the Excel files present in john folder.

解决方案

The workbook which contains the UserForm that you want to display should also have a procedure which displays the form. You will need to call this procedure to display the userform. It can be a function or a sub, I prefer function because then you can return a success/failure for error-handling.

In the UserForm workbook, you'll add a procedure like this in Module1 (or any module, but you'll need to reference this later):

Public Function ShowTheForm(Optional Modal As Boolean = False)
    'API to display a userform in THIS workbook, from another workbook

     On Error Resume Next
     UserForm1.Show IIF(Modal,vbModal,vbModeless)
     ShowTheForm = (Err.Number = 0)
End Function

Then, in the workbook which is trying to call this form open, you will need to invoke the ShowTheForm procedure, like so:

Do While file <> ""
    Set wb = Application.Workbooks.Open(file)
    If Not IsEmpty(wb) Then
        Application.Visible = False
        Application.Run("'" & wb.Name & "'!Module1.ShowTheForm")
    End If
    wb.Close
    file = Dir()
Loop

Because you've given ShowTheForm as a function with a return value, you can trap errors, for example:

If Not Application.Run("'" & wb.Name & "'!Module1.ShowTheForm") Then
    MsgBox "Unable to display..."
    Exit Sub
End If

Modified/enhanced based on the general logic provided here:

http://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-do-you-open-userform-in-another-workbook/e97b2c06-2a79-4cef-89bc-4f67b0f3c03a?db=5&auth=1

NOTE

I think IsEmpty is not the appropriate test on a workbook object, you may want to look in to that. I'm not sure what you're trying to do with that line, but I'm almost certain it's not doing what you think it's doing.

这篇关于从一个文件调用另一个UserForm的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！