Search code examples
excelvbaexcel-formulatextbox

Inserting TextBox Values into a Formula

I was hoping you could help me find the solution to my macro coding. The textbox values are not being entered into the formula when pasted into the active cell.

Here is my coding:

ActiveCell.Formula = “=“”BOX “”&IF(OR(M3=1,M3=3),N3+TextBox1,N3-TextBox1)”

I want the cells end result to look something like this: BOX 20

Thanks

Solution

  • Do not use those fancy quotes, as VBA does not understand them.

    Variables cannot be inside of quotes, so use this instead:

    ActiveCell.Formula = "=""BOX ""&IF(OR(M3=1,M3=3),N3+" & TextBox1 & ",N3-" & TextBox1 & ")"

    Keep in mind that if your textbox has something other than a number in it, or it is blank, you will get an error.

    If IsNumeric(TextBox1) Then
  ActiveCell.Formula = "=""BOX ""&IF(OR(M3=1,M3=3),N3+" & TextBox1 & ",N3-" & TextBox1 & ")"
Else
  MsgBox "'" & TextBox1 & "' is not a numeric value."
End If

    If you compare my answer to your question, do you see how your formula is all red? That means it's all part of a literal string, so there are no variables in it, and since your textbox is a variable, it needs to be outside of the quotes.

    In my code, see how the TextBox1 variable is not red?