用户表单-从私有子目录返回到公共子目录 [英] Userform - Return to public sub from private sub

问题描述

我有以下代码，这是一个私有子代码，它是从Userform3.show初始化为一个公共子代码，据我所知，下面的代码可以工作，但是在完成时不会返回到公共子代码.

请注意，当我更改sheet8.range(I16)的值时，将调用另一个私有子项.但是我相信下面的代码是问题.

任何人都可以建议我如何让私人潜艇在完成后返回公共潜艇吗?

---

  Private子UserForm_Initialize()'用木板填充组合框和我在一起.清除'清除先前的项目(不要有双打").AddItem"BISSB".AddItem"MORIB".AddItem"RMIB"结束于结束子 

---

  Private Sub CommandButton1_Click()如果Me.ComboBox1.ListIndex = -1然后UserForm3.HideMsgBox未选择板，请重新运行宏并选择适当的板"退出子别的Sheet8.Range("I16").Value = ComboBox1.Text万一结束子 

---

  Private Sub CommandButton2_Click()UserForm3.HideMsgBox未选择板，请重新运行宏并选择适当的板"结尾结束子 

---

  Private Sub UserForm_QueryClose(取消为整数，CloseMode为整数)如果CloseMode = 0，则MsgBox未选择板，请重新运行宏并选择适当的板"结尾万一结束子 

开始公开订阅

  Sub newResumeAssessment_Click()变暗答案为变体昏暗进行为布尔昏暗的Uname作为字符串如果UCase(Sheets("Main Menu").Range("A1"))="YES"，则答案= 6别的答案= MsgBox(单击是"以开始业务案例."& _vbCrLf&单击否以恢复业务案例."&vbCrLf&_单击取消返回到主菜单."&vbNewLine&_vbNewLine&请注意，您需要加载董事会提交的内容"&_开始新业务案例之前的跟踪器."，35，业务案例")万一如果答案= 6则UserForm3.Show 

解决方案

第一件事，在任何地方删除 End . End 是一个大红色的NUKE'EM ALL按钮，它会在此处结束执行，然后-在 End 执行完之后，无论在哪里您在通话堆栈中，因为 不再有通话堆栈 ；您的代码不再运行，无处可返回".

第二件事，不要将状态保留在表单的默认实例中.像对待对象一样对待它，并在需要时使用 New 创建一个新实例-这样，您就不必为 Unload 加载它和/或烦恼重置调用之间的状态:每次都会运行 _Initialize 处理程序，并且无需清除上次调用中的项目，因为您将每次都使用一个新实例.您是这样做的:

  With New UserForm3'UserForm_Initialize处理程序在此处运行.Show'UserForm_Activate处理程序在这里运行'.Show之后的所有内容仅在关闭表单后运行如果没有，则取消Sheet8.Range("I16").Value = .ComboBox1.Text万一以'UserForm_Terminate处理程序结尾在此处运行 

请注意，工作表不是通过表单写入的-这不是它的工作！那么我们如何使该 Cancelled 成员合法?

首先，您命名事物，并使 CommandButton1 为 OkButton 和 CommandButton2 为 CancelButton -或其他任何东西-只是不包括Button1和Button2.

我喜欢您正在隐藏表单实例，而不是使用 Unload Me 对其进行修改，但是您正在明确地工作再次是默认实例，这意味着上面的 New UserForm3 代码将不会隐藏正在显示的实例.当您打算与 Me 一起使用时，从不用默认实例限定成员通话.

换句话说:

  UserForm3.Hide'隐藏UserForm3的默认实例Me.Hide'隐藏当前实例隐藏与我同在 

所以.添加一个 Private isCancelled As Boolean 私有字段(模块级变量)，然后公开一个 Public Property Get Cancelled()As Boolean 公共属性getter并返回它:

 选项显式私有isCancelled为布尔值公共属性Get Cancelled()为布尔值已取消= isCancelled最终财产 

接下来，让您的取消按钮设置标志:

  Private Sub CancelButton_Click()isCancelled =真我隐藏结束子 

然后使 QueryClose 处理程序也进行设置-并尽可能使用现有的命名常量:

  Private Sub UserForm_QueryClose(取消为整数，CloseMode为整数)如果CloseMode = vbFormControlMenu，则取消=正确isCancelled =真我隐藏万一结束子 

然后在确定"按钮的处理程序中实现快乐路径"逻辑:

  Private Sub OkButton_Click()我隐藏结束子 

在用户做出选择之前，我将禁用确定"按钮-这样，他们便可以取消，取消输出或进行有效选择！

 公共属性获取SelectedBoard()作为字符串SelectedBoard = IIf(Me.ComboBox1.ListIndex = -1，vbNullString，Me.ComboBox1.Text)最终财产私人子ComboBox1_Change()验证表结束子私人子ValidateForm()Me.OkButton.Enabled =(SelectedBoard<> vbNullString)结束子私人子UserForm_Activate()验证表结束子 

现在，呼叫者可以看起来像这样:

 使用新的UserForm3.表演如果没有，则取消Sheet8.Range("I16").Value = .SelectedBoard别的MsgBox未选择板，请重新运行宏并选择适当的板"万一结束于 

现在，您拥有的表单不过是代码的I/O设备而已.而且您正在使用对象而不是全局状态.

TL; DR:

• 在任何地方删除 End 指令.
• 不要在 UserForm3 内引用 UserForm3 (改为使用 Me ).
• 每次都使用一个新的 New 实例.
• 公开 Property Get 成员以供调用代码访问，以抽象出调用者不需要关心的控件.
• 不允许表单处于无效状态.

I have the below code which is a private sub which is initialized from Userform3.show in a public sub, as far as I am aware the code below works however it does not return to the public sub on completion.

Please note when I change the value of sheet8.range(I16) another private sub is called. However I believe the below code is the issue.

Can anyone advise how I can get the private sub to return to the public sub on completion?

---

Private Sub UserForm_Initialize()
'populate "Combo-Box with Boards

With Me.ComboBox1
.Clear ' clear previous items (not to have "doubles")
.AddItem "BISSB"
.AddItem "MORIB"
.AddItem "RMIB"
End With
End Sub

---

Private Sub CommandButton1_Click()

If Me.ComboBox1.ListIndex = -1 Then
UserForm3.Hide
MsgBox "No board was selected, please re-run macro and select appropriate board"
Exit Sub

Else
Sheet8.Range("I16").Value = ComboBox1.Text

End If
End Sub

---

Private Sub CommandButton2_Click()
UserForm3.Hide
MsgBox "No board was selected, please re-run macro and select appropriate board"
End
End Sub

---

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
 If CloseMode = 0 Then
    MsgBox "No board was selected, please re-run macro and select appropriate board"
    End
    End If
End Sub

Start of Public Sub

Sub newResumeAssessment_Click()
Dim answer As Variant
Dim Proceed As Boolean
Dim Uname As String

If UCase(Sheets("Main Menu").Range("A1")) = "YES" Then

answer = 6

Else

    answer = MsgBox("Click Yes to start a Business Case." & _
      vbCrLf & "Click No to resume the Business Case." & vbCrLf & _
      "Click Cancel to go back to the main menu." & vbNewLine & _
      vbNewLine & "Please note, you will need to load the board submission " & _
      "tracker before you start a new business case.", 35, "Business Case")

End If

If answer = 6 Then

UserForm3.Show

解决方案

First thing, remove End, everywhere. End is a big red NUKE 'EM ALL button that ends execution right there and then - after End has executed, it doesn't matter where you were in the call stack, for there is no call stack anymore; your code isn't running anymore, there's nowhere to "return" to.

Second thing, don't keep state in the default instance of a form. Treat it like the object it is, and New up a new instance when you need one - that way you don't need to be bothered with Unloading it and/or resetting the state between calls: the _Initialize handler will run every time, and there won't be a need to Clear the items from the previous call, since you'll be working with a fresh instance every time. You do that like this:

With New UserForm3 'UserForm_Initialize handler runs here
    .Show 'UserForm_Activate handler runs here
    'anything after .Show will only run after the form is closed
    If Not .Cancelled Then
        Sheet8.Range("I16").Value = .ComboBox1.Text
    End If
End With 'UserForm_Terminate handler runs here

Notice the worksheet is NOT being written to by the form - it's not its job! So how do we make that Cancelled member legal?

First you name things and make CommandButton1 be OkButton and CommandButton2 be CancelButton - or whatever - just not Button1 and Button2.

I like that you're hiding the form instance instead of nuking it with Unload Me, however you're explicitly working off the default instance again, which means the above New UserForm3 code will not be hiding the same instance that's being shown. NEVER qualify member calls with a default instance when you mean to work with Me.

In other words:

UserForm3.Hide 'hides the default instance of UserForm3

Me.Hide 'hides whatever the current instance is

Hide 'same as Me.Hide

So. Add a Private isCancelled As Boolean private field (module-level variable), and then expose a Public Property Get Cancelled() As Boolean public property getter that returns it:

Option Explicit
Private isCancelled As Boolean

Public Property Get Cancelled() As Boolean
    Cancelled = isCancelled
End Property

Next, make your cancel button set the flag:

Private Sub CancelButton_Click()
    isCancelled = True
    Me.Hide
End Sub

Then make the QueryClose handler set it too - and use the existing named constants wherever possible:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        Cancel = True
        isCancelled = True
        Me.Hide
    End If
End Sub

Then implement your "happy-path" logic in the OK button's handler:

Private Sub OkButton_Click()
    Me.Hide
End Sub

I would disable the OK button until the user makes a selection - that way they can either cancel, x-out, or make a valid selection!

Public Property Get SelectedBoard() As String
    SelectedBoard = IIf(Me.ComboBox1.ListIndex = -1, vbNullString, Me.ComboBox1.Text)
End Property

Private Sub ComboBox1_Change()
    ValidateForm
End Sub

Private Sub ValidateForm()
    Me.OkButton.Enabled = (SelectedBoard <> vbNullString)
End Sub

Private Sub UserForm_Activate()
    ValidateForm
End Sub

And now the caller can look like this:

With New UserForm3
    .Show
    If Not .Cancelled Then
        Sheet8.Range("I16").Value = .SelectedBoard
    Else
        MsgBox "No board was selected, please re-run macro and select appropriate board"
    End If
End With

And now you have a form that's nothing more than an I/O device for your code, as it should be. And you're using objects instead of global state.

TL;DR:

• Remove End instruction wherever they are.
• Don't reference UserForm3 inside UserForm3 (use Me instead).
• Work with a fresh New instance every time.
• Expose Property Get members for the calling code to access, to abstract away controls that the caller doesn't need to care about.
• Don't allow the form to be okayed in an invalid state.

这篇关于用户表单-从私有子目录返回到公共子目录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！