代码之家 › 专栏 › 技术社区 › Sean Bailey

Userform-从私有sub返回公共sub

userform excel vba

Sean Bailey · 技术社区 · 7 年前

我有下面的代码,这是一个私有子,从Userform3初始化。在公共sub中显示,据我所知,以下代码有效,但在完成后不会返回公共sub。

当我更改表8的值时,请注意。范围(I16)称为另一个私有子对象。然而,我相信下面的代码就是问题所在。

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

公共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

2 回复 | 直到 6 年前

Mathieu Guindon 7 年前

去除 End 处处是一颗红色的核弹当场执行,然后执行 终止 不再有调用堆栈

第二件事 一种形式。像对待物体一样对待它,并且 New Unload 调用和/或重置调用之间的状态: _Initialize 处理程序每次都会运行,不需要 Clear 来自上一个调用的项,因为您每次都将使用一个新实例。你这样做:

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

请注意,表单没有写入工作表- 这不是它的工作 ! 那我们怎么做呢 Cancelled 会员合法?

首先是你并制作 CommandButton1 是 OkButton CommandButton2 是 CancelButton

我喜欢你这样躲藏表单实例,而不是使用 Unload Me ,然而 明确地 正在处理 默认实例 New UserForm3 代码不会隐藏正在显示的同一个实例。从未 Me .

换句话说:

UserForm3.Hide 'hides the default instance of UserForm3

Me.Hide 'hides whatever the current instance is

Hide 'same as Me.Hide

Private isCancelled As Boolean Public Property Get Cancelled() As Boolean 返回它的公共属性获取程序:

Option Explicit
Private isCancelled As Boolean

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

接下来,让取消按钮设置标志:

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

QueryClose handler也设置了它,并尽可能使用现有的命名常量:

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

Private Sub OkButton_Click()
    Me.Hide
End Sub

我会禁用OK按钮,直到用户做出选择-这样他们可以取消、x-out或做出有效选择!

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

现在调用方可以如下所示:

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

现在你有了一个表单,它只不过是你的代码的输入输出设备,正如它应该的那样。你使用的是对象而不是全局状态。

去除 终止
UserForm3 在…内 (使用 我 相反)。
与新鲜人一起工作实例。
暴露 Property Get
不允许表单在无效状态下被确定。

Tim Williams 7 年前

除非你以非模态方式显示表单, 在表单关闭之前,打开代码始终停止