1. Providing your text is made up of "normal" (that is, non-Unicode
characters with ASCII values of less than 256), then there is a non-looping,
one-line alternative to the Mid function looping code the other respondents
have given you...

C = Split(Trim(Replace(StrConv(YourText, vbUnicode), Chr(0), " ")))

Just assign your text to the YourText variable, execute the code above and
the C variable (which could be declared either as a Variant or as a dynamic
String array) will contain an array in which each of its elements contain a
character from the text you assigned. For example...

Sub TestCharacterSplitting()
Dim YourText As String, C As Variant, X As Long
YourText = "1234"
C = Split(Trim(Replace(StrConv(YourText, vbUnicode), Chr(0), " ")))
' Show the contents of the array of characters in C
For X = 0 To UBound(C)
Debug.Print "<" & C(X) & ""
Next
End Sub

will print each of the digits in "1234" on its own line with angle brackets
around it (to show you only one character is assigned to each element of the
array) in the Immediate window of the VB editor.

--
Rick (MVP - Excel)


"jay dean" wrote in message
...

Hello -

I have 3 questions:

1. How do I use VBA to split text without delimiters into tokens?
Example: Cell A1 contains the text/string "1234". How do I use vba to
split it and place the tokens '1' in B1, '2' in C1, '3' in D1, '4' in
E4? I thought about using the Split() function but these strings don't
have delimiters.

2. When changing the background color of a textbox on a userform, I
used, for example, TextBox1.BackColor = vbBlue. However, that is not the
kind of blue I want. How do I change the code to get a background of one
of the colors in the palette, like navy blue, dark green,e.t.c? Is there
a reference similar to "colorindex" matching a color to a number that I
may use?

3. In Sheet("Z").Range("A1:A20"), I have strings. How do I use vba to
add the contents of each cell (i.e an item) in the range into a textbox
on a userform AND how do I do the same for a listbox on a userform.

Any help would be greatly appreciated.

Thanks
Jay




*** Sent via Developersdex http://www.developersdex.com ***

Thank you Rick, Ron, Patrick, and Bernard !!
I really appreciate this.

Jay Dean

*** Sent via Developersdex http://www.developersdex.com ***