Showing a calculation in a userform Textbox

[jamie.rowland]

Ok...

I am trying to make a text box (aptly named txtCalc) to show a
calculation between another txt box (txtAmount) and a cell that exists
in the spreadsheet behind the form. The calculation will show a bank
balance preview.

For example the cell that is mentioned above is Cell F1 and contains a
figure of £9000

In the userform, the user enters a transaction of - £500 in txtAmount
field. thus the txtCalc area automatically shows a figure of £8500.
This figure does not have to go onto the spreadsheet (but can if
needed).

How can this be done...

Cheers (much in advance)

Jamie

 

[james.billy]

Hi Jamie,

You just need to put some code behind the on change event or the exit
event of the text box your entering the figure into. In design mode
just double click on txtamount and it should take you to the correct
event.

You would then put something like:

me.textcalc = Worksheets("Sheet1").Range("F1") - me.txtAmount

Cheers,

James

 

[raypayette]

I supposed that you made an error and the result is 35500.
Note that the UserForm1 must have the ShowModal property as False.
The following code should be in the Sheet1 code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.txtCalc.Text = ActiveSheet.Cells(1, 1).Text
UserForm1.txtAmount.Text = ActiveSheet.Cells(2, 1).Value
UserForm1.txtResult.Text = ActiveSheet.Cells(1, 1) -
ActiveSheet.Cells(2, 1)
End Sub

 

[Robert Bruce]

Ok...

I am trying to make a text box (aptly named txtCalc) to show a
calculation between another txt box (txtAmount) and a cell that exists
in the spreadsheet behind the form. The calculation will show a bank
balance preview.

For example the cell that is mentioned above is Cell F1 and contains a
figure of £9000

In the userform, the user enters a transaction of - £500 in txtAmount
field. thus the txtCalc area automatically shows a figure of £8500.
This figure does not have to go onto the spreadsheet (but can if
needed).

Are you saying that the user will input /literally/ the string "- £500" in
the textbox, operator and all?

It would make this a whole lot easier if you could get them to leave the
currency symbol out. Presumably the currency symbol on the value in the
worksheet is only there as a result of formatting?

Then you could use something like:

txtCalc.value = Application.Evaluate(cstr(range("F1")) & txtAmount.Value)

....though you're going to have to include a fair amount of validation and
error trapping.