PDA

View Full Version : how to show 4 digit number



maryam
05-16-2007, 06:13 AM
how to show the text in 4 digit number ?
I am manipulating textboxes with the data I have in the worksheet. How can I show the numbers in 4 digit in the text box, while I keep them as the exact number?

sujittalukde
05-16-2007, 07:13 AM
Assuming the fig is in cell A1, insert the following formula in cell B! =left(a1,4).Draw a text box, select the box ane click to formula bar and write =b1.Done Hope this will solve your qwery

maryam
05-16-2007, 07:31 AM
I couldn't understand. I dont want to change the data I have in the worksheet, I just want to cut them to 4 digit when I am manipulating the textboxes which are on the userform.

lucas
05-16-2007, 07:36 AM
select the textbox in the vbe and look for maxlength in the properties for the textbox....

mdmackillop
05-16-2007, 11:49 AM
Dim MyNumber As Long

Private Sub UserForm_Initialize()
MyNumber = Cells(1, 1)
TextBox1 = Left(Application.Text(MyNumber, "0000"), 4)
End Sub

maryam
05-16-2007, 09:33 PM
I mean I want to manipulate a textbox with 6.76895543234445 for example, but I want 6.768 to be seen, while the real number is there. Is it possible?

lucas
05-16-2007, 10:07 PM
If the number is in the spreadsheet..(the long one with all digits) then you can show the short one in the textbox but use the long one in the spreadsheet range for your calculations....

mdmackillop
05-16-2007, 11:46 PM
Private Sub UserForm_Initialize()
Dim MyVal As Double
MyVal = Range("A1").Value
TextBox1 = Round(MyVal, 4)
Range("A2") = MyVal * 100
End Sub

maryam
05-17-2007, 12:21 AM
lucas what should I put for maxlength? It doesnt work when I change the maxlength!

lucas
05-17-2007, 08:29 AM
Malcolm has a great solution in post #8...It shows the shortened number in the userform but does calculations from the value in cell A1. if you want to return it in a textbox on the form you could do it like this(MyVal is declared as public):
Option Explicit
Dim MyVal As Double
Private Sub CommandButton1_Click()
TextBox2 = MyVal * 100
End Sub
Private Sub UserForm_Initialize()
MyVal = Range("A1").Value
TextBox1 = Round(MyVal, 4)
End Sub

maryam
05-19-2007, 01:12 AM
what if I just want to show 4 digits (not for decimal), just up to 4 digits for example 3.444566 as 3.444?

zv735
05-19-2007, 01:37 AM
Try to use

TextBox1 = [Round(A1, 4 - Len(IF(ROUNDDOWN(A1, 0)=0,"",ROUNDDOWN(A1, 0))))]

Cell A1 as the exact number

Thank you

mdmackillop
05-19-2007, 02:57 AM
Hi Maryam
On the face of it use
TextBox1 = Round(MyVal, 3) Can you clarify "4 digits". How about 20.46, 123.4 etc? Will these ever occur?
This should cater for such
Private Sub UserForm_Initialize()
Dim MyVal As Double
MyVal = Range("A1").Value
TextBox1 = Round(MyVal, 4 - Len(CStr(Int(MyVal))))
Range("A2") = MyVal * 100
End Sub

maryam
05-19-2007, 05:21 AM
IFI use this, how will large numbers be shown?

mdmackillop
05-19-2007, 06:03 AM
We can use the Worksheet function to show significant figures
TextBox1 = Application.WorksheetFunction.Round(MyVal, 4 - Len(CStr(Int(MyVal))))
We can show whatever you need, but I'm not clear what that is.

Aussiebear
05-19-2007, 02:05 PM
If you set the textbox format to 4 digits then I'm assuming it will show 9999 as a maximum value, i.e., without a decimal point

mdmackillop
05-19-2007, 02:17 PM
If you set the textbox format to 4 digits then I'm assuming it will show 9999 as a maximum value, i.e., without a decimal point
Have you been playing with Access?

lucas
05-19-2007, 02:24 PM
I don't think anything that modern has made it that far south yet.:devil2:

Aussiebear
05-19-2007, 03:52 PM
Give us a break, will you. "Gone with the Wind" just opened at the local theatre yesterday.