Solved: Formatting a number in TextBox

[MagicMike]

Hello there!

This is my first post. I have been using this site for a little while now and I have found alot and learned alot so thank you to all of you! I'm basically stumped on a quite simple task that is blowing my mind for tha past 2 days.

I have a textbox that is being populated from 1 cell on a worksheet.
The cell has a number in it that is 6 numbers long with no spaces "123456"
I can get the text to the textbox ok but I need the textbox to display the number as "123.456" with a period not a comma.
Not sure if this helps but the cell already has custom formatting to display the number properly there.

Heres what I have so far which yeids the result "123456.":


Private Sub TextBox_Next_Avail_WO_Change()
TextBox_Next_Avail_WO = Format(TextBox_Next_Avail_WO.Value, "###.")
End Sub


I'm sure this is simple but I cannot seem to understand where I went wrong here.

Thanks in advance!

-Mike

[Norie]

Mike

If the cell is already formatted on the worksheet as you want it use the Text property when you populate the textbox.

[MagicMike]

I knew it was something easy! Thank you! I've been refreshing the screen every minute waiting for a reply lol.

I used this where I bring the code to the textbox

TextBox_Next_Avail_WO = Sheets("260").Range("A6").Text + 1
I did have the value in there and not text as you suggested.
I was affraid it would not work due to me adding a number to it but it works fine.

Thanks again!

[Bob Phillips]

Why did you not just use the same formatting mask that you used in the spreadsheet?

[Kenneth Hobs]

While numberformat would not work in format in all cases, it generally works. I would do it as Bob suggested.
With Worksheets("Sheet1").Range("A1")
TextBox1.Value = Format(.Value + 1, .NumberFormat)
End With

[MagicMike]

Hello gentlemen,

I'm bringing this back to life as I have encountered a small promlem with the textbox that is displaying the cell data. I am getting the decimal place in the correct place but when the value "600.010" is in the cell the text box displays "600.01" and it drops off the 0. Is there a way to force the textbox to display the last zero?

Thanks again in advance.

-Mike

[Kenneth Hobs]

What code did you use for the Textboxes Change event? The code that I posted will fix it.

[MagicMike]

Hello Kenneth, thanks for responding, I'm a relative newbie so thanks for your time.

I am working with this so:


TextBox_Next_Avail_WO = Sheets("260").Range("A6").Text + 0.001


The cell A6 contains a number that has the value of "600010" as an example. The cell formatting is set to display the value as text "600.010".

When I tried TextBox_Next_Avail_WO.value it seems to have no effect.

Does this info help?

[Norie]

This any use?

TextBox_Next_Avail_WO = Val(Sheets("260").Range("A6").Text) + 0.001

Note this isn't going to help with the formatting, so you'll need to use some of the other suggestions for that.:)

[MagicMike]

This any use?

TextBox_Next_Avail_WO = Val(Sheets("260").Range("A6").Text) + 0.001

Note this isn't going to help with the formatting, so you'll need to use some of the other suggestions for that.:)

Tried that with the same result, thanks anyway :)

[Bob Phillips]

Try this



TextBox_Next_Avail_WO = Sheets("260").Range("A6").Text & 0.001

[MagicMike]

Try this



TextBox_Next_Avail_WO = Sheets("260").Range("A6").Text & 0.001


Tried this also

Result is that it adds 0.001 as text at the end of the data and does not do the addition.

Thanks anyway

[Bob Phillips]

I thought that was what you wanted :-)

Maybe this?



TextBox_Next_Avail_WO = Format(Sheets("260").Range("A6").Text + 0.001, "###.000")

[MagicMike]

I thought that was what you wanted :-)

Maybe this?



TextBox_Next_Avail_WO = Format(Sheets("260").Range("A6").Text + 0.001, "###.000")


YES, thank you that is what I need! It works!

-Mike