Prev: Validate to quarter units
Next: Type checking from VBA to C++?
From: Beancounter on 25 Feb 2010 13:49
I have a userform list box - the data it is using to populate the list
box contains part numbers - some of which are text and some of which
are numbers. How do I set it so no matter what type of part number is
selected, it will show as text?

Thanks in advance.
From: JLGWhiz on 25 Feb 2010 14:53
One way would be to apply the CStr function to each value of the ListBox as
you use it. For example

Range("A1") = CStr(ListBox1.Value)

Or if MultiSelect

Range("A1") = CStr(ListBox1.List(0)

The CStr function forces the data to string type and if it is already string
type it ignores it.

"Beancounter" <dennis.mccarthy(a)us.atlascopco.com> wrote in message
news:bcb89e33-1e1b-42ca-924c-73bff078665c(a)q21g2000yqm.googlegroups.com...
>I have a userform list box - the data it is using to populate the list
> box contains part numbers - some of which are text and some of which
> are numbers. How do I set it so no matter what type of part number is
> selected, it will show as text?
>
> Thanks in advance.


From: Dave Peterson on 25 Feb 2010 16:24
By the time your data is in the listbox, it's all text.

Maybe you're asking how to make sure you populate the listbox with entries that
retain the leading 0's???

If yes, you could use something like (assuming you're looping through a range)
this in the userform_initialize procedure:

dim myRng as range
dim myCell as range

set myrng = worksheets("Sheet99").range("a1:A10") 'whatever

for each mycell in myrng.cells
'if it's formatted correctly already in the cell
me.listbox1.additem mycell.text
'or if you want to specify the format
'use the format you like
me.listbox1.additem format(mycell.value, "00000000")
next mycell




Beancounter wrote:
>
> I have a userform list box - the data it is using to populate the list
> box contains part numbers - some of which are text and some of which
> are numbers. How do I set it so no matter what type of part number is
> selected, it will show as text?
>
> Thanks in advance.

--

Dave Peterson
 | 
Pages: 1
Prev: Validate to quarter units
Next: Type checking from VBA to C++?