Breaking Cell Formula into Multiple Pieces

[TedSki]

I have a cell that totals cash drops but I want to create a form to create the formula, place it into a cell and then display the pieces on the form.

The forumla is :

=100.00+5000.00+25000.00+10.05

Form will have value1 textbox and value2 textbox and value3 textbox etc.

When you click the save it will concatenate the "=" and value1 and "+" and value2 etc. and place the formula into the cell.

When the form first opens, I would like to break out each value that currently exists and display it in the correct textbox. The amounts can be any size - usually from about 25,000 to as low as 1.00.

Any ideas?

 

[pbornemeier]

This code should go in the userform that contains 4 text boxes (txtValue1 .....) and a Command Button named cmdSave

Private Sub cmdSave_Click()

    ActiveSheet.Range("A1").Formula = "=" & txtValue1 & "+" & txtValue2 & "+" & txtValue3 & "+" & txtValue4
    Me.Hide
    
End Sub

Private Sub UserForm_Activate()

    Dim varValues As Variant
    Dim sFormula As String
    
    sFormula = ActiveSheet.Range("A1").Formula
    varValues = Split(sFormula, "+")
    varValues(0) = Mid(varValues(0), 2, 1000)
    
    Me.txtValue1 = varValues(0)
    Me.txtValue2 = varValues(1)
    Me.txtValue3 = varValues(2)
    Me.txtValue4 = varValues(3)
    
End Sub

 

[TedSki]

Thanks for the response, Phil. I will try you code as soon as I can.