| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Label = whole number - How do I set this?
Private Sub UserForm_Initialize() Label2.Caption = Worksheets("Data").Range("j20").Value End Sub I am using this to draw calculated data from my worksheet and past into my UserForm. I set my work sheet cell J20 up to use whole number only, but when my UserForm calls the data up it is still being tol decimal values. Instead of 125.5 showing up in my user form I want t see just 125 How can I do this please? Also any suggested reading web sites for someone just wetting thei feet in this -- Baf ----------------------------------------------------------------------- Bafa's Profile: http://www.excelforum.com/member.php...fo&userid=3774 View this thread: http://www.excelforum.com/showthread.php?threadid=57362 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
Not sure if this is the best way to do it but here is a way to do it: Private Sub UserForm_Initialize() Dim num As Integer num = Worksheets("Data").Range("J20").Value Label2.Caption = num End Sub Have a go and see how it goes -- Steel Monke ----------------------------------------------------------------------- Steel Monkey's Profile: http://www.excelforum.com/member.php...fo&userid=2905 View this thread: http://www.excelforum.com/showthread.php?threadid=57362 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
Works great thanks! -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573620 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
No worries, glad it worked! -- Steel Monkey ------------------------------------------------------------------------ Steel Monkey's Profile: http://www.excelforum.com/member.php...o&userid=29051 View this thread: http://www.excelforum.com/showthread...hreadid=573620 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
On your spreadsheet you have probably formatted the cell to show whole
numbers, but that doesn't actually change the cell value. You can format in VBA as well before you display the value. Try: Label2.Caption = Format(Worksheets("Data").Range("j20").Value,"#") Note: This is also a format and will not change the actual value. Hope this helps, Dan "Bafa" wrote: Private Sub UserForm_Initialize() Label2.Caption = Worksheets("Data").Range("j20").Value End Sub I am using this to draw calculated data from my worksheet and paste into my UserForm. I set my work sheet cell J20 up to use whole numbers only, but when my UserForm calls the data up it is still being told decimal values. Instead of 125.5 showing up in my user form I want to see just 125 How can I do this please? Also any suggested reading web sites for someone just wetting their feet in this? -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573620 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
Bafa,
If you want what appears in the cell, use the .Text property. If you want the actual value stored in the cell, use the .Value property Label2.Caption = Worksheets("Data").Range("j20").Text Or you can format the .Value how you wish Label2.Caption = Format(Worksheets("Data").Range("j20").Value,"0" NickHK "Bafa" wrote in message ... Private Sub UserForm_Initialize() Label2.Caption = Worksheets("Data").Range("j20").Value End Sub I am using this to draw calculated data from my worksheet and paste into my UserForm. I set my work sheet cell J20 up to use whole numbers only, but when my UserForm calls the data up it is still being told decimal values. Instead of 125.5 showing up in my user form I want to see just 125 How can I do this please? Also any suggested reading web sites for someone just wetting their feet in this? -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573620 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
Format works, but 125,5 is changed to 126. That's the same as Round( Worksheets("Data").Range("j20").Value, 0 ) He asked for 125. Label2.Caption = Left(LTrim(Str(Worksheets("Data").Range("j20").Val ue)), InStr(1, LTrim(Str(Worksheets("Data").Range("j20").Value)), ".", vbTextCompare) - 1) will give 125. "Dan Hatola" schreef in bericht ... On your spreadsheet you have probably formatted the cell to show whole numbers, but that doesn't actually change the cell value. You can format in VBA as well before you display the value. Try: Label2.Caption = Format(Worksheets("Data").Range("j20").Value,"#") Note: This is also a format and will not change the actual value. Hope this helps, Dan "Bafa" wrote: Private Sub UserForm_Initialize() Label2.Caption = Worksheets("Data").Range("j20").Value End Sub I am using this to draw calculated data from my worksheet and paste into my UserForm. I set my work sheet cell J20 up to use whole numbers only, but when my UserForm calls the data up it is still being told decimal values. Instead of 125.5 showing up in my user form I want to see just 125 How can I do this please? Also any suggested reading web sites for someone just wetting their feet in this? -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573620 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
I guess I just assumed that the 126 was a typo. If only integer portion of
the number is desired, the INT function would probably be a little easier: Label2.Caption = Int(Worksheets("Data").Range("j20").Value) That being said, I think Nick HK's suggestion to use ".text" instead of ".value" is the best solution. Dan "moon" wrote: Format works, but 125,5 is changed to 126. That's the same as Round( Worksheets("Data").Range("j20").Value, 0 ) He asked for 125. Label2.Caption = Left(LTrim(Str(Worksheets("Data").Range("j20").Val ue)), InStr(1, LTrim(Str(Worksheets("Data").Range("j20").Value)), ".", vbTextCompare) - 1) will give 125. "Dan Hatola" schreef in bericht ... On your spreadsheet you have probably formatted the cell to show whole numbers, but that doesn't actually change the cell value. You can format in VBA as well before you display the value. Try: Label2.Caption = Format(Worksheets("Data").Range("j20").Value,"#") Note: This is also a format and will not change the actual value. Hope this helps, Dan "Bafa" wrote: Private Sub UserForm_Initialize() Label2.Caption = Worksheets("Data").Range("j20").Value End Sub I am using this to draw calculated data from my worksheet and paste into my UserForm. I set my work sheet cell J20 up to use whole numbers only, but when my UserForm calls the data up it is still being told decimal values. Instead of 125.5 showing up in my user form I want to see just 125 How can I do this please? Also any suggested reading web sites for someone just wetting their feet in this? -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573620 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
I love how active this forum is. I am learning a lot. The .tex solution should work just fine for my needs. Thanks to all wh answered -- Baf ----------------------------------------------------------------------- Bafa's Profile: http://www.excelforum.com/member.php...fo&userid=3774 View this thread: http://www.excelforum.com/showthread.php?threadid=57362 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Label = whole number - How do I set this?
or this
Label2.Caption = Fix(Worksheets("Data").Range("j20").Value) -- Gary "Bafa" wrote in message ... Private Sub UserForm_Initialize() Label2.Caption = Worksheets("Data").Range("j20").Value End Sub I am using this to draw calculated data from my worksheet and paste into my UserForm. I set my work sheet cell J20 up to use whole numbers only, but when my UserForm calls the data up it is still being told decimal values. Instead of 125.5 showing up in my user form I want to see just 125 How can I do this please? Also any suggested reading web sites for someone just wetting their feet in this? -- Bafa ------------------------------------------------------------------------ Bafa's Profile: http://www.excelforum.com/member.php...o&userid=37748 View this thread: http://www.excelforum.com/showthread...hreadid=573620 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting number label | Excel Discussion (Misc queries) | |||
| attach a label to number, like validation but follows the number | Excel Worksheet Functions | |||
| Removal of number label from x-axis | Charts and Charting in Excel | |||
| Is there a way to have two values (percent and number) in a label. | Charts and Charting in Excel | |||
| how to remove label formatting (eg label to number) | Excel Worksheet Functions | |||
![Bafa[_7_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
