带有用户窗体的 Excel VBA If/Then 语句

Excel VBA If/Then Statement with UserForm

提问by ryano

So I've done some Javascript editing before but it's been a while since then. I know I should know how to do this but the solution has escaped me. All I'm trying to do is make an ElseIf command so that certain formulas are put into cells that need them based on values from a UserForm.

所以我之前做过一些 Javascript 编辑，但从那时起已经有一段时间了。我知道我应该知道如何做到这一点，但解决方案却让我望而却步。我要做的就是创建一个 ElseIf 命令，以便根据用户窗体中的值将某些公式放入需要它们的单元格中。

This is what I have:

这就是我所拥有的：

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 Trim(Me.txtItem.Value) = "" Then
  Me.txtItem.SetFocus
  MsgBox "Please enter an item"
  End If

 If Trim(Me.txtPerc.Value) = "" Then
'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With table_object_row
'  .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, 8).Formula = "=F*E"

 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
  If Trim(Me.txtAdjust.Value) = "" Then

  With table_object_row
  .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).Formula = "=(C*(1-D))"
  .Range(1, 6).Value = Me.txtQTY.Value
  .Range(1, 8).Formula = "=F*E"




'  .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

回答by Chris Rolliston

You need to use nested Ifstatements:

您需要使用嵌套If语句：

Else
  If Trim(Me.txtPerc.Value) = "" Then
    ' code...
  End If
End If

As an aside, your current code is also missing an End With(there are two Withlines but only one End With) and an explicit declaration of table_list_object.

顺便说一句，您当前的代码还缺少一个End With（有两With行但只有一行End With）和table_list_object.

回答by Tylor Hess

You never End Ifand there is only one End With(wish I could comment this, but don't have that ability yet).

你从来没有End If，只有一个End With（希望我能评论这个，但还没有那种能力）。

If Trim(Me.txtItem.Value) = "" Then
  Me.txtItem.SetFocus
  MsgBox "Please enter an item"


ElseIf Trim(Me.txtPerc.Value) = "" Then
    'copy the data to the database
    'use protect and unprotect lines,
    '     with your password
    '     if worksheet is protected
    With table_object_row
    '  .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, 8).Formula = "=F5*E5"
    End With
End If

If Trim(Me.txtAdjust.Value) = "" Then

    With table_object_row
        .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).Formula = "=(C5*(1-D5))"
        .Range(1, 6).Value = Me.txtQTY.Value
        .Range(1, 8).Formula = "=F5*E5"

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

回答by Juliusz

Three options here.

这里有三个选项。

1. Use classic FormulaR1C1so in your code it would be:

   .Range(1, 8).FormulaR1C1= "=RC[-1]*RC[-2]"

2. Use built-in behaviour of Excel Table (List Object in VBA), where functions are retained within a table. So if you add a new row and do not populate a field, the formula will be automatically added for you.

3. Use FormulaR1C1with the twist, where you reference values by table and column names. For example, if your table is called Table1and columns (in the header) are 'Col1' and 'Col2' you can put:

   .Range(1, 8).FormulaR1C1= "=Table1[@Col1]*Table1[@Col2]"

1. FormulaR1C1在您的代码中使用经典，因此将是：

   .Range(1, 8).FormulaR1C1="=RC[-1]*RC[-2]"

2. 使用 Excel 表格（VBA 中的列表对象）的内置行为，其中函数保留在表格中。因此，如果您添加新行但未填充字段，则会自动为您添加公式。

3. FormulaR1C1与扭曲一起使用，您可以在其中按表名和列名引用值。例如，如果您的表被调用Table1并且列（在标题中）是“Col1”和“Col2”，您可以输入：

   .Range(1, 8).FormulaR1C1="=Table1[@Col1]*Table1[@Col2]"

or simpler:

或更简单：

.Range(1, 8).FormulaR1C1= "=[@Col1]*[@Col2]" 

I usually go either with the second or third option.

我通常选择第二个或第三个选项。