Excel VBA If/Then Statement with UserForm

Question

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.

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

Then what is the question? You already do .Range(1, 8).Formula = "=F5*E5", e.g. Perhaps you have to tell what object in the UserForm you want to get the value from (you are already doing that as well). — sancho.s ReinstateMonicaCellio
– sancho.s ReinstateMonicaCellio, Commented Nov 21, 2013 at 16:42
I know I should know how to do this but the solution has escaped me. Did you do your research? Did you check msdn or inbuilt Excel help or Google on the syntax of IF? — Siddharth Rout
– Siddharth Rout, Commented Nov 21, 2013 at 16:54

Chris Rolliston · Accepted Answer · 2013-11-21 22:58:33Z

1

You need to use nested If statements:

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 With lines but only one End With) and an explicit declaration of table_list_object.

edited Nov 21, 2013 at 22:58

answered Nov 21, 2013 at 16:42

Chris Rolliston

4,8181 gold badge19 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tylor Hess · Accepted Answer · 2013-11-21 17:07:24Z

1

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

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

edited Nov 21, 2013 at 17:07

answered Nov 21, 2013 at 16:55

Tylor Hess

6795 silver badges16 bronze badges

2 Comments

Siddharth Rout Over a year ago

but don't have that ability yet Then you should wait till the time you have enough reputation.

Siddharth Rout Over a year ago

I see you have edited your post :) Why not make it a full fledged answer :) This way if it gets accepted or upvoted then your reputation will also increase? :)

Juliusz · Accepted Answer · 2013-11-21 16:44:19Z

0

Three options here.

Use classic FormulaR1C1 so in your code it would be:

.Range(1, 8).FormulaR1C1= "=RC[-1]*RC[-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.
Use FormulaR1C1 with the twist, where you reference values by table and column names. For example, if your table is called Table1 and columns (in the header) are 'Col1' and 'Col2' you can put:

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

answered Nov 21, 2013 at 16:44

Juliusz

2,1052 gold badges29 silver badges34 bronze badges

2 Comments

ryano Over a year ago

Thanks guys seems to be working now. New question though. There is a limitation in the current code because as I insert a new column the equation doesn't adjust for the new line. So if it inserts at line 3 and then line 4 I need that number to adjust accordingly.

Juliusz Over a year ago

Try the third method - it will adjust, as it uses the column names.

Collectives™ on Stack Overflow

Excel VBA If/Then Statement with UserForm

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related