excel - 用户窗体没有正确关闭

Question

原始问题：我有一个打开的用户表单，输入了一些信息，并且有一个取消和确定按钮。当我单击取消时，它会卸载用户窗体并关闭窗口，然后空白窗口再次打开？

更新：我以为我只是搞砸了一些东西，将所有代码复制并粘贴到一个新的工作簿中就可以了。它似乎进行了一些初步测试，现在它又在做同样的事情。

我猜它与所有 textbox_exit 潜艇有关吗？

在主工作表上有一个显示 userform1 的格式按钮。输入了一些信息，按钮选项是取消和确定。Cancel 应该只是卸载 userform1 并隐藏它。OK 继续到 userform3。（userform2 已被删除，我从未重命名它们）

输入 Userform3 更多信息，然后再次出现 Cancel 和 OK。OK 应该运行宏的其余部分，并且 cancel 应该卸载并隐藏 userform3。

当我单击 userform3 上的任一按钮时，它会卸载表单（预期）然后表单关闭并立即 userform1 重新打开（意外/不需要）

当我在 userform1 上单击确定时，取消 userform1 卸载并消失，然后立即重新打开（卸载/空白）弹出？

已修复，但仍有疑问。正如下面的评论中提到的，这被复制到一个新的工作簿中，它似乎可以自行修复。然后无论出于何种原因，它又开始做同样的事情。我周五下班了，并没有三思而后行。现在星期一它似乎工作？有谁知道是什么原因导致需要点击两次取消才能使表格消失？

这是userform1上的代码

Option Explicit
Public Sub UserForm_Initialize()

    'centers the userform on the active excel screen
    Me.StartUpPosition = 0
    Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
    Me.top = Application.top + (0.5 * Application.Height) - (0.5 * Me.Height)


    Sheets("hidden").Range("A2:C2").Clear

End Sub
Private Sub Cancel_UserForm1_Click()
Unload Me
End Sub
Public Sub OK_UserForm1_Click()

    Dim chk As Control
    Dim c, i, j, k, m As Long

    'START DATE populated
    If TextBox1 = "" Then
                'SEQUENCE populated (get double error at same time rather than 2 individual errors)
                If TextBox2 = "" Then
                            'PROPNUM COLUMN populated (get triple error at same time)
                            If TextBox8 = "" Then
                                        'all 3 blank
                                        TextBox1.BackColor = RGB(255, 153, 153)
                                        TextBox2.BackColor = RGB(255, 153, 153)
                                        TextBox8.BackColor = RGB(255, 153, 153)
                                        TextBox1.SetFocus
                                        MsgBox "Please enter start date, beginning sequence number, and propnum column"
                                        Exit Sub
                            Else
                                        'first 2 blank
                                        TextBox1.BackColor = RGB(255, 153, 153)
                                        TextBox2.BackColor = RGB(255, 153, 153)
                                        TextBox1.SetFocus
                                        MsgBox "Please enter start date and beginning sequence number"
                                        Exit Sub
                            End If
                Else
                '####################################
                '##      START DATE is blank       ##
                '##  SEQUENCE filled in correctly  ##
                '####################################
                            'PROPNUM COLUMN populated
                            If TextBox8 = "" Then
                                        TextBox1.BackColor = RGB(255, 153, 153)
                                        TextBox8.BackColor = RGB(255, 153, 153)
                                        TextBox1.SetFocus
                                        MsgBox "Please enter start date and propnum column"
                                        Exit Sub
                            Else
                                        TextBox1.BackColor = RGB(255, 153, 153)
                                        TextBox1.SetFocus
                                        MsgBox "Please enter start date"
                                        Exit Sub
                            End If
                End If
    Else
    '####################################
    '## START DATE filled in correctly ##
    '####################################
                'SEQUENCE populated
                If TextBox2 = "" Then
                            'PROPNUM COLUMN populated
                            If TextBox8 = "" Then
                                        TextBox2.BackColor = RGB(255, 153, 153)
                                        TextBox8.BackColor = RGB(255, 153, 153)
                                        TextBox2.SetFocus
                                        MsgBox "Please enter beginning sequence and propnum column"
                                        Exit Sub
                            Else
                                        TextBox2.BackColor = RGB(255, 153, 153)
                                        TextBox2.SetFocus
                                        MsgBox "Please enter beginning sequence number"
                                        Exit Sub
                            End If
                Else
                '####################################
                '## START DATE filled in correctly ##
                '##  SEQUENCE filled in correctly  ##
                '####################################
                            'PROPNUM COLUMN populated
                            If TextBox8 = "" Then
                                        TextBox8.BackColor = RGB(255, 153, 153)
                                        TextBox8.SetFocus
                                        MsgBox "Please enter the propnum column"
                                        Exit Sub
                            End If
                End If
    End If

    'checks CheckBox's to see if they are checked (checked = 1, not checked = 0)
    For Each chk In Me.Controls
        If TypeOf chk Is MSForms.CheckBox Then
            If chk = 0 Then
                i = i + 1
            End If
        End If
    Next

    'if they were all 0 (not checked) this will be 13
    If i = 13 Then
        MsgBox "Please select at least one keyword category"
        Exit Sub
    End If



    'saves the selected number of keyword categories and start date
    Sheets("hidden").Visible = True
    Sheets("hidden").Select
    Sheets("hidden").Range("A2").Value = TextBox1.text
    Sheets("hidden").Range("B2").Value = TextBox2.text
    Sheets("hidden").Range("C2").Value = TextBox8.text



    'saves the CheckBox keywords
    For j = 1 To 13
        'what this box checked (1 is checked)
        m = 0
        If Me.Controls("CheckBox" & j) = 1 Then
            Sheets("hidden").Cells((3 + m), 1).Value = Me.Controls("CheckBox" & j).Caption
            m = m + 1
        End If
    Next j


    'i is the number of categories that were checked
    For c = 1 To 13
        'puts the variables into the spreadsheet
        With Me

                'once all checks are done clear out any previously saved KEYWORDS
                If c = 1 Then
                    k = 4
                    Sheets("hidden").Visible = True
                    Sheets("hidden").Select
                    Range(Cells(k, 1), Cells(k + 9, 3)).Select
                    Range(Cells(k, 1), Cells(k + 9, 3)).Clear
                    Sheets("hidden").Cells(4, 3).Value = Sheets("hidden").Cells(2, 2).Value
                Else
                End If
                'KEYWORDS and line continuations get saved
                If Me.Controls("CheckBox" & c).Object.Value = True Then
                    'If c = 1 Or c = 2 Then
                        Sheets("hidden").Cells(k, 1).Value = .Controls("CheckBox" & c).Caption
                        Sheets("hidden").Cells(k, 2).Value = .Controls("TextBox" & (k - 1)).text
                        Sheets("hidden").Cells((k + 1), 3).Value = Sheets("hidden").Cells(k, 2).Value + Sheets("hidden").Cells(k, 3).Value + 1
                    k = k + 1
                End If
        End With
    Next c

    'remove last KSS cell -- it isnt needed
    Cells(k, 3).Clear

    Unload Me

    Unload UserForm3
    UserForm3.Show

    Sheets("Imported Data").Select

End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'checks START DATE if there is something in it, if its blank skip check
    If TextBox1 <> "" Then
        'START DATE a date
        If Not IsDate(TextBox1) Then
            TextBox1.BackColor = RGB(255, 153, 153)
            TextBox1.SetFocus
            MsgBox "Please enter start date in MM/YYYY format"
            Exit Sub
        Else
            If Not IsNumeric(Right(TextBox1.Value, 4)) Then
                TextBox1.BackColor = RGB(255, 153, 153)
                TextBox1.SetFocus
                MsgBox "Please enter start date in MM/YYYY format"
                Exit Sub
            Else
                If Right(TextBox1.Value, 4) > 2050 Then
                    TextBox1.SetFocus
                    MsgBox "Are you sure about your date?"
                End If
            End If
        End If
    End If

    'changes color back to white for correct
    TextBox1.BackColor = RGB(255, 255, 255)


End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'checks SEQUENCE if there is something in it, if its blank skip check
    If TextBox2 <> "" Then
        'BEGINNING SEQUENCE a number and not blank
        If Not IsNumeric(TextBox2) Then
            TextBox2.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter a numeric beginning sequence number"
            Exit Sub
        End If
        'BEGINNING SEQUENCE a positive number
        If TextBox2 < 1 Then
            TextBox2.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter a positive beginning sequence"
            Exit Sub
        Else
            'BEGINNING SEQUENCE trim decimals
            If TextBox2 > 0 And TextBox2 < 10 Then
                TextBox2.Value = Left(TextBox2, 1)
            Else
                TextBox2.Value = Left(TextBox2, 2)
            End If
        End If
    End If

    'changes color back to white for correct
    TextBox2.BackColor = RGB(255, 255, 255)

End Sub
Private Sub TextBox8_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'checks PROPNUM COLUMN if it is entered in correct format, if its blank skip check
    If TextBox8 <> "" Then
        'START DATE a date
        If IsNumeric(TextBox8) Then
            TextBox8.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter propnum column in Y format"
            Exit Sub
        End If
    End If

    'changes color back to white for correct
    TextBox8.BackColor = RGB(255, 255, 255)


End Sub
Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'if the box is blank skip the check
    If TextBox3 <> "" Then
        'CONTINUATION LINE numeric
        If Not IsNumeric(TextBox3) Then
            TextBox3.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter a number into the UNIQUE GAS continuation line box"
            Exit Sub
        Else
            'CONTINUATION LINE positive
            If TextBox3 < 0 Then
                TextBox3.BackColor = RGB(255, 153, 153)
                MsgBox "Please enter a positive number into the UNIQUE GAS continuation line box"
                Exit Sub
            Else
                'CONTINUATION LINE less than 6
                If TextBox3 > 5 Then
                    TextBox3.BackColor = RGB(255, 153, 153)
                    MsgBox "Plese enter a number less than 6 into the UNIQUE GAS continuation line box"
                    Exit Sub
                Else
                    'CONTINUATION LINE whole number
                    TextBox3.Value = Left(TextBox3, 1)
                End If
            End If
        End If
    End If

    'CONTINUATION LINE box white for correct
    TextBox3.BackColor = RGB(255, 255, 255)

End Sub
Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'if the box is blank skip the check
    If TextBox4 <> "" Then
        'CONTINUATION LINE numeric
        If Not IsNumeric(TextBox4) Then
            TextBox4.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter a number into the OIL continuation line box"
            Exit Sub
        Else
            'CONTINUATION LINE positive
            If TextBox4 < 0 Then
                TextBox4.BackColor = RGB(255, 153, 153)
                MsgBox "Please enter a positive number into the OIL continuation line box"
                Exit Sub
            Else
                'CONTINUATION LINE less than 6
                If TextBox4 > 5 Then
                    TextBox4.BackColor = RGB(255, 153, 153)
                    MsgBox "Plese enter a number less than 6 into the OIL continuation line box"
                    Exit Sub
                Else
                    'CONTINUATION LINE whole number
                    TextBox4.Value = Left(TextBox4, 1)
                End If
            End If
        End If
    End If

    'CONTINUATION LINE box white for correct
    TextBox4.BackColor = RGB(255, 255, 255)

End Sub
Private Sub TextBox5_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'if the box is blank skip the check
    If TextBox5 <> "" Then
        'CONTINUATION LINE numeric
        If Not IsNumeric(TextBox5) Then
            TextBox5.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter a number into the GAS continuation line box"
            Exit Sub
        Else
            'CONTINUATION LINE positive
            If TextBox5 < 0 Then
                TextBox5.BackColor = RGB(255, 153, 153)
                MsgBox "Please enter a positive number into the GAS continuation line box"
                Exit Sub
            Else
                'CONTINUATION LINE less than 6
                If TextBox5 > 5 Then
                    TextBox5.BackColor = RGB(255, 153, 153)
                    MsgBox "Plese enter a number less than 6 into the GAS continuation line box"
                    Exit Sub
                Else
                    'CONTINUATION LINE whole number
                    TextBox5.Value = Left(TextBox5, 1)
                End If
            End If
        End If
    End If

    'CONTINUATION LINE box white for correct
    TextBox5.BackColor = RGB(255, 255, 255)

End Sub
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'if the box is blank skip the check
    If TextBox6 <> "" Then
        'CONTINUATION LINE numeric
        If Not IsNumeric(TextBox6) Then
            TextBox6.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter a number into the WATER continuation line box"
            Exit Sub
        Else
            'CONTINUATION LINE positive
            If TextBox6 < 0 Then
                TextBox6.BackColor = RGB(255, 153, 153)
                MsgBox "Please enter a positive number into the WATER continuation line box"
                Exit Sub
            Else
                'CONTINUATION LINE less than 6
                If TextBox6 > 5 Then
                    TextBox6.BackColor = RGB(255, 153, 153)
                    MsgBox "Plese enter a number less than 6 into the WATER continuation line box"
                    Exit Sub
                Else
                    'CONTINUATION LINE whole number
                    TextBox6.Value = Left(TextBox6, 1)
                End If
            End If
        End If
    End If

    'CONTINUATION LINE box white for correct
    TextBox6.BackColor = RGB(255, 255, 255)

End Sub
Private Sub TextBox7_Exit(ByVal Cancel As MSForms.ReturnBoolean)

    'if the box is blank skip the check
    If TextBox7 <> "" Then
        'CONTINUATION LINE numeric
        If Not IsNumeric(TextBox7) Then
            TextBox7.BackColor = RGB(255, 153, 153)
            MsgBox "Please enter a number into the NGL continuation line box"
            Exit Sub
        Else
            'CONTINUATION LINE positive
            If TextBox7 < 0 Then
                TextBox7.BackColor = RGB(255, 153, 153)
                MsgBox "Please enter a positive number into the NGL continuation line box"
                Exit Sub
            Else
                'CONTINUATION LINE less than 6
                If TextBox7 > 5 Then
                    TextBox7.BackColor = RGB(255, 153, 153)
                    MsgBox "Plese enter a number less than 6 into the NGL continuation line box"
                    Exit Sub
                Else
                    'CONTINUATION LINE whole number
                    TextBox7.Value = Left(TextBox7, 1)
                End If
            End If
        End If
    End If

    'CONTINUATION LINE box white for correct
    TextBox7.BackColor = RGB(255, 255, 255)

End Sub

标签： excelvbauserform