Text box in form

[raw]

Hi guys

I have a text box in a user form that take is linked to sheet1! G3

But G3 had the formula that works out the prices from different sheets
and show the answer. I can only seem to use the text box once it shows
the number but Deletes the formula and just leaves the figure in the
cell G3 I just need the text box as a display box?

Also i can not seem to get the text box to display £ and the 0 on the
end of pence e.g. 123.1 and it should be £123.10

Thanks for your help

 

[dominicb]

Good evening raw

We can solve both of these with one line of code:

TextBox1.Text = Format(Range("Sheet1!G3").Value, "£#,###.00")

However, this is probably a bit of a diversion to the way you've
probably done it, especially if you've linked the textbox to the value
property, but this is the better way to go.

If you struggle to implement this post back and we'll take it from
there.

HTH

DominicB

 

[raw]

Hi Thanks for the code

This maybe a stupied question but where do you put this code?

Thanks

 

[dominicb]

Hi raw

Well, it really depends on how your spreadsheet is set up.

Say, you're using UserForm1 and your textbox is called TextBox1 and you
have a macro calling Userform1:

Sub Test()
UserForm1.Show
End Sub

From the VBE double click on the UserForm you've designed and copy this
code into the pane that opens:

Private Sub UserForm_Initialize()
TextBox1.Text = Format(Range("Sheet1!G3").Value, "£#,###.00")
End Sub

Now, when you run the macro "Test", UserForm1 should open and TextBox1
will contain the value of G3 formatted in £ and pence.

HTH

DominicB

 

[raw]

Hi Thanks for that

Worked

Thank you

 

[dominicb]

Hi raw

You're welcome. Thanks for the feedback.

DominicB