如果UserForm框为空，则停止插入表中的数据

So what I'm trying to achieve is pretty basic. Basically, I have some text boxes in a UserForm that insert the Values into a table on a sheet. There are some basic requirement that need to be achieved before those values are put into that table. The code below should explain what I'm trying to do.

所以我想要实现的是非常基本的。基本上,我在UserForm中有一些文本框,可以将值插入到工作表的表中。在将这些值放入该表之前,需要实现一些基本要求。下面的代码应该解释我正在尝试做什么。

The problem I'm running into is that, when a textbox is empty and the error message pops up (based on my criteria), after you hit ok to the message the code continues to write the rest of the data to the sheet minus that one UserForm box that didn't have data in it originally and then clears all the forms. What I would like for it to do is keep the data and when you hit "Ok" it takes you to where you need to complete the form. (SetFocus)

我遇到的问题是,当一个文本框为空并且弹出错误消息时(根据我的标准),在你点击消息之后,代码继续将剩余的数据写入工作表减去一个最初没有数据的UserForm框,然后清除所有表单。我想要它做的是保存数据,当你点击“确定”时,它会带你到你需要填写表格的地方。 (SetFocus的)

Private Sub addItem_Click()
Dim the_sheet As Worksheet
Dim table_object_row As ListRow
Set the_sheet = Sheets("NewOrder")

'find first empty row in database
Set table_list_object = the_sheet.ListObjects(1)
Set table_object_row = table_list_object.ListRows.Add


'check for a part number
If Me.taxBox = True Then
    Tax = "N"
End If

If Trim(Me.txtItem.Value) = "" Then
  Me.txtItem.SetFocus
  MsgBox "Enter an item"
        Else
        If Trim(Me.txtSKU.Value) = "" Then
            Me.txtSKU.SetFocus
         MsgBox "Enter SKU"

            Else
            If Trim(Me.txtPerc.Value) = "" And Trim(Me.txtAdjust.Value) = "" Then
             Me.txtPerc.SetFocus
             MsgBox "Enter percent or adjusted price"

                Else
                If Trim(Me.txtPrice.Value) = "" And Trim(Me.txtAdjust.Value) = "" Then
                 Me.txtPrice.SetFocus
                MsgBox "Enter original price"
                End If
            End If
        End If
    End If


If Trim(Me.txtPerc.Value) = "" And Me.txtAdjust.Value > 0 Then
With table_object_row
the_sheet.Unprotect Password:="password"

  .Range(1, 1).Value = Me.txtItem.Value
  .Range(1, 2).Value = Me.txtSKU.Value
  .Range(1, 3).Value = Me.txtPrice.Value
  .Range(1, 4).Value = Me.txtPerc.Value
  .Range(1, 5).Value = Me.txtAdjust.Value
  .Range(1, 6).Value = Me.txtQTY.Value
  .Range(1, 7).Value = Tax

  the_sheet.Protect Password:="password"
 End With

 'clear the data
Me.txtItem.Value = ""
Me.txtSKU.Value = ""
Me.txtPrice.Value = ""
Me.txtPerc.Value = ""
Me.txtAdjust.Value = ""
Me.txtQTY.Value = ""





  Else
  If Trim(Me.txtAdjust.Value) = "" And Me.txtPrice.Value > 0 And Me.txtPerc.Value > 0 Then

  With table_object_row
the_sheet.Unprotect Password:="password"
  .Range(1, 1).Value = Me.txtItem.Value
  .Range(1, 2).Value = Me.txtSKU.Value
  .Range(1, 3).Value = Me.txtPrice.Value
  .Range(1, 4).Value = Me.txtPerc.Value
  .Range(1, 6).Value = Me.txtQTY.Value
  .Range(1, 7).Value = Tax
the_sheet.Protect Password:="password"
End With


'clear the data
Me.txtItem.Value = ""
Me.txtSKU.Value = ""
Me.txtPrice.Value = ""
Me.txtPerc.Value = ""
Me.txtAdjust.Value = ""
Me.txtQTY.Value = ""
Me.txtItem.SetFocus
End If
End If

End Sub

2 个解决方案

#1

The way your code is structured, once an error box is ndisplayed and OK'd, the code continues to process further logic.

代码的构造方式,一旦显示错误框并且OK,代码将继续处理进一步的逻辑。

Also, there are a number of other structural issues. Below is a refactor, including some '*** comments noting changes.

此外,还有许多其他结构问题。下面是一个重构,包括一些'***评论注意到变化。

    Dim the_sheet As Worksheet
    Dim table_object_row As ListRow

'*** Declare all your variables
    Dim table_list_object As ListObject
    Dim Tax As Variant

    Set the_sheet = Sheets("NewOrder")

    'find first empty row in database
    Set table_list_object = the_sheet.ListObjects(1)
    '** move this to after checks
    'Set table_object_row = table_list_object.ListRows.Add


    'check for a part number
    If Me.taxBox = True Then
        Tax = "N"
    End If

    '*** Use If Then ElseIf structure
    If Trim(Me.txtItem.Value) = "" Then
        Me.txtItem.SetFocus
        MsgBox "Enter an item"
    ElseIf Trim(Me.txtSKU.Value) = "" Then
        Me.txtSKU.SetFocus
        MsgBox "Enter SKU"
    ElseIf Trim(Me.txtPerc.Value) = "" And Trim(Me.txtAdjust.Value) = "" Then
        Me.txtPerc.SetFocus
        MsgBox "Enter percent or adjusted price"
    ElseIf Trim(Me.txtPrice.Value) = "" And Trim(Me.txtAdjust.Value) = "" Then
        Me.txtPrice.SetFocus
        MsgBox "Enter original price"
    Else
        '*** Only execute this if no blanks are found
        If Trim(Me.txtPerc.Value) = "" And Me.txtAdjust.Value > 0 Then
            '*** Move create new row to where you are certain to add data
            Set table_object_row = table_list_object.ListRows.Add
            With table_object_row
                'the_sheet.Unprotect Password:="password"

                .Range(1, 1).Value = Me.txtItem.Value
                .Range(1, 2).Value = Me.txtSKU.Value
                .Range(1, 3).Value = Me.txtPrice.Value
                .Range(1, 4).Value = Me.txtPerc.Value
                .Range(1, 5).Value = Me.txtAdjust.Value
                .Range(1, 6).Value = Me.txtQTY.Value
                .Range(1, 7).Value = Tax

                'the_sheet.Protect Password:="password"
            End With

            'clear the data
            Me.txtItem.Value = ""
            Me.txtSKU.Value = ""
            Me.txtPrice.Value = ""
            Me.txtPerc.Value = ""
            Me.txtAdjust.Value = ""
            Me.txtQTY.Value = ""
        ElseIf Trim(Me.txtAdjust.Value) = "" And Me.txtPrice.Value > 0 And Me.txtPerc.Value > 0 Then
            '*** Move create new row to where you are certain to add data
            Set table_object_row = table_list_object.ListRows.Add
            With table_object_row
                'the_sheet.Unprotect Password:="password"
                .Range(1, 1).Value = Me.txtItem.Value
                .Range(1, 2).Value = Me.txtSKU.Value
                .Range(1, 3).Value = Me.txtPrice.Value
                .Range(1, 4).Value = Me.txtPerc.Value
                .Range(1, 6).Value = Me.txtQTY.Value
                .Range(1, 7).Value = Tax
                'the_sheet.Protect Password:="password"
            End With

            'clear the data
            Me.txtItem.Value = ""
            Me.txtSKU.Value = ""
            Me.txtPrice.Value = ""
            Me.txtPerc.Value = ""
            Me.txtAdjust.Value = ""
            Me.txtQTY.Value = ""
            Me.txtItem.SetFocus
        Else
            '*** what if we reach here?
            MsgBox "What Now?"
        End If
    End If
End Sub

#2

@Chris Neilsen Thanks the code worked great. I did have the sheet protected so I had to move a couple of lines around to accommodate that. Where I needed to, this is what I ended up with:

@Chris Neilsen感谢代码工作得很好。我确实保护了这张纸,所以我不得不移动几条线以适应这种情况。在我需要的地方,这就是我最终得到的结果:

the_sheet.Unprotect Password:="password"
   Set table_object_row = table_list_object.ListRows.Add
   With table_object_row