vba 从模块关闭用户窗体

Close UserForm from Module

提问by Addy

I am trying to Close a User Form from a module, but it's not working.

我正在尝试从模块中关闭用户表单，但它不起作用。

Here is what I have tried

这是我尝试过的

Sub UpdateSheetButton()
    Dim subStr1 As String
    Dim subSrrt2() As String
    Dim tmp As Integer
    Dim pos As Integer

    Dim Form As WaitMessage
    Set Form = New WaitMessage

    With Form
        .Message_wait = Module2.Label_PleaseWait  
        .Show
    End With

    For Each Cell In ActiveSheet.UsedRange.Cells
        subStr1 = RemoveTextBetween(Cell.formula, "'C:\", "\AddIns\XL-EZ Addin.xla'!")
        tmp = Len(subStr1) < 1
        If tmp >= 0 Then
            Cell.formula = subStr1
            status = True
        End If
    Next

    Unload Form

    MsgBox Module2.Label_ProcessComplete

End Sub

Form Name is WaitMessage.

表单名称是WaitMessage.

I have also tried WaitMessage.Hidebut it's also not working.

我也尝试过，WaitMessage.Hide但它也不起作用。

采纳答案by dee

Another possibility could be to put your code to ClassModuleand to use Eventsto callback to WaitMessage user form. Here short example. HTH

另一种可能性是将您的代码放入ClassModule并使用Events回调到 WaitMessage 用户表单。这里是简短的例子。HTH

Standard modulecreates the form and the updater object and displays the form which starts processing:

标准模块创建表单和更新程序对象并显示开始处理的表单：

Public Sub Main()
    Dim myUpdater As Updater
    Dim myRange As Range
    Dim myWaitMessage As WaitMessage

    Set myRange = ActiveSheet.UsedRange.Cells
    Set myUpdater = New Updater
    Set myUpdater.SourceRange = myRange

    ' create and initialize the form
    Set myWaitMessage = New WaitMessage

    With myWaitMessage
        .Caption = "Wait message"
        Set .UpdaterObject = myUpdater
        ' ... etc.
        .Show
    End With

    MsgBox "Module2.Label_ProcessComplete"
End Sub

Class modulecontaines the monitored method and has events which are raised if progress updated or finished. In the event some information is send to the form, here it is the number of processed cells but it can be anything else:

类模块包含受监视的方法，并具有在进度更新或完成时引发的事件。如果某些信息被发送到表单，这里是已处理单元格的数量，但它可以是其他任何内容：

Public Event Updated(updatedCellsCount As Long)
Public Event Finished()
Public CancelProcess As Boolean
Public SourceRange As Range

Public Sub UpdateSheetButton()
    Dim subStr1 As String
    Dim subSrrt2() As String
    Dim tmp As Integer
    Dim pos As Integer
    Dim changesCount As Long
    Dim myCell As Range
    Dim Status

    ' process task and call back to form via event and update it
    For Each myCell In SourceRange.Cells

        ' check CancelProcess variable which is set by the form cancel-process button
        If CancelProcess Then _
            Exit For

        subStr1 = "" ' RemoveTextBetween(Cell.Formula, "'C:\", "\AddIns\XL-EZ Addin.xla'!")
        tmp = Len(subStr1) < 1
        If tmp >= 0 Then
            myCell.Formula = subStr1
            Status = True
        End If

        changesCount = changesCount + 1
        RaiseEvent Updated(changesCount)

        DoEvents
    Next

    RaiseEvent Finished
End Sub

User formhas instance of updater class declared with 'WithEvent' keyword and handles events of it. Here form updates a label on 'Updated' event and unloads itself on 'Finished' event:

用户表单具有使用 'WithEvent' 关键字声明的更新程序类的实例并处理它的事件。这里表单更新了“更新”事件的标签，并在“完成”事件上自行卸载：

Public WithEvents UpdaterObject As Updater

Private Sub UpdaterObject_Finished()
    Unload Me
End Sub

Private Sub UpdaterObject_Updated(updatedCellsCount As Long)
    progressLabel.Caption = updatedCellsCount
End Sub

Private Sub UserForm_Activate()
    UpdaterObject.UpdateSheetButton
End Sub

Private Sub cancelButton_Click()
    UpdaterObject.CancelProcess = True
End Sub

回答by Julian Evans

Considering a modeless form, create a subroutine within the userform:

考虑一个无模式表单，在用户表单中创建一个子程序：

Sub UnloadThisForm ()
    unload me
End Sub

and call the sub from outside the userform;

并从用户窗体外部调用子程序；

call Userform1.UnloadThisForm

回答by Patrick Lepelletier

i guess you can do yourself the screenupdating and enableevents, so here is :

我想你可以自己做 screenupdating 和 enableevents，所以这里是：

(next time add more description to what you are trying to do, and no just post code...)

（下次为您尝试做的事情添加更多描述，而不仅仅是发布代码......）

Option Explicit 'might help to avoid future miss declaring of variables...

Sub UpdateSheetButton()
Dim subStr1 As String
'Dim subSrrt2() As String 'not used in shown code !
'Dim tmp As Integer
Dim pos As Long
Dim Cell as Range 'you forgot to declare this

Dim Form As object
Set Form = New WaitMessage

load Form
With Form
    .Message_wait = Module2.Label_PleaseWait  
    .Show false 'if you ommit false, the code won't continue from this point unless the Form is closed !
End With

For Each Cell In ActiveSheet.UsedRange.Cells
    subStr1 = RemoveTextBetween(Cell.formula, "'C:\", "\AddIns\XL-EZ Addin.xla'!")
    'tmp = Len(subStr1) < 1 'might replace with a boolean (true/false, instead 0/-1)
    'If tmp >= 0 Then 'you don't use tmp later, so i guess its just using variables without need
     if substr1<>"" then 'why use a cannon to put a nail in a wall?, go to the point
        Cell.formula = subStr1
        pos = pos+1 'you declared pos but didn't use it !?
        Form.SomeTextbox.caption = pos 'or other counter
        'can also use the .width property of a button or picture... to make a progression bar.
        status = True 'Status is not declared , and not used elsewhere , so what ?!
    End If
Next

Unload Form
set Form = Nothing

MsgBox Module2.Label_ProcessComplete

End Sub

回答by Sam

A userform is an object in it's own right, you do not need to declare or set as a variable. Also, when you use the .Show Method it will set the Modal property to True by default, which will pause code execution until the user interacts in some way (i.e. closes the form).

用户窗体本身就是一个对象，您不需要声明或设置为变量。此外，当您使用 .Show 方法时，默认情况下它会将 Modal 属性设置为 True，这将暂停代码执行，直到用户以某种方式进行交互（即关闭表单）。

You can get around this by using a boolean declaration after the .Show method to specify if the userform is to be shown modal.

您可以通过在 .Show 方法之后使用布尔声明来指定用户窗体是否显示为模态来解决此问题。

Try this instead:

试试这个：

Sub UpdateSheetButton()
Dim subStr1 As String
Dim subSrrt2() As String
Dim tmp As Integer
Dim pos As Integer

With WaitMessage
    .Message_wait = Module2.Label_PleaseWait
    .Show False
End With

For Each Cell In ActiveSheet.UsedRange.Cells
    subStr1 = RemoveTextBetween(Cell.Formula, "'C:\", "\AddIns\XL-EZ Addin.xla'!")
    tmp = Len(subStr1) < 1
    If tmp >= 0 Then
        Cell.Formula = subStr1
        Status = True
    End If
Next

Unload WaitMessage

MsgBox Module2.Label_ProcessComplete

End Sub