MrExcel.com - Your One Stop for Excel Tips & Solutions

Format Textbox with formula

Posted by Dave on October 23, 2001 11:56 AM

Hello again, I'd like to thank everyone for the help. This group is Great.

Here's the problem I have two textboxes on a Userform the value
of the first is used in a formula to get the value of second. I have the formula
in a cell and run this macro to copy the value to another cell which is linked
to the second textbox. This works fine(even changing the value in the first from
the textbox or worksheet)

I would like to Format the second Textbox as I do the first (the formating is
correct in the first).
BUT This is not working......please help.


Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("c8").Copy
Sheets("Sheet1").Range("d8").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub

Private Sub UserForm_Initialize()
Me.TextBox1 = Format(Me.TextBox1, "#,##0")
Me.TextBox2 = Format(Me.TextBox2, "#,##0")
End Sub

Private Sub TextBox1_Change()
Me.TextBox1 = Format(Me.TextBox1, "#,##0")
End Sub

Private Sub TextBox2_Change()
Me.TextBox2 = Format(Me.TextBox2, "#,##0")
End Sub

Posted by Damon Ostrander on October 24, 2001 12:24 PM

Hi Dave,

I believe the reason why this is not working is that since TextBox2 is linked to a cell containing a formula, your

Me.TextBox2 = Format(Me.TextBox2, "#,##0")

cannot be allowed as it would also have to modify the cell's value, which conflicts with the value the formula is yielding.

My recommendation is to not link TextBox2 to a cell, but establish a "virtual link" by setting its value in the worksheet's calculate event, which would become:

Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Sheets("Sheet1").Range("c8").Copy
Sheets("Sheet1").Range("d8").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Me.TextBox2 = Format([d8], "#,##0")
Application.CutCopyMode = False
End Sub

You would also have to delete TextBox2 change event code.

Another option would be to simply do the entire calculation right in the TextBox1 change event code. If the function being performed is not horrendously complicated, this would be the cleanest way to do the whole thing. For example, say that the value in D8 is the square root of the value in C8. The the whole thing could be done by simply using this code for the TextBox1 change event:

Private Sub TextBox1_Change()
Me.TextBox1 = Format(Sqr([C8]), "#,##0")
End Sub

This would eliminate the need to go the the worksheet for the calculation entirely.

Happy computing.

Damon