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

问题描述：

所以我试图实现的是非常基本的。基本上，我有一些用户窗体中的文本框，将值插入到工作表中的表格中。在将这些数值放入该表格之前，有一些基本要求需要实现。下面的代码应该解释我正在尝试做什么。如果UserForm框为空，则停止插入表中的数据

我遇到的问题是，当一个文本框是空的，并弹出错误消息（根据我的标准），当你点击确定消息后代码继续写入其余的数据该工作表减去最初没有数据的一个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

答

代码被构造，一旦出错框ndisplayed和判决同意的方式，代码继续处理其它逻辑。

另外，还有一些其他的结构性问题。下面是一个重构，其中包括一些'***评论注意到的变化。

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

答

@Chris Neilsen谢谢代码运行良好。我确实保护了纸张，所以我必须移动几行来适应这种情况。在我需要的地方，这是我最终得到的：

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

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

相关推荐