Text Boxes

[waggy30]

I have two problems.

The first is is it possible to have six textboxes, that all go to the same cell. For example six names all going to a cell, all separated by a comma.

The second is it possible to use that cell, and use it as inputs for six textboxes. Basically The reverse of the first problem.

 

[waggy30]

Desperately need help

 

[gplhl]

you mean get each text box to be in control of part of an array?

Would it not be easeier to use 6 different cells and then concatenate these into another cell?
_________________
Regards,

Gary Hewitt-Long
This message was edited by gplhl on 2002-03-07 11:09

 

[waggy30]

Thanks. I couldn't get it to work. I don't know how to create arrays. However, i found a different way to solve problem 1.

To solve problem two, I was thinking about using some code to search for commas and then return the value i want.

For example, in one cell i have

Peter, Jim, Bob, Fred, Geoff, Jack


What I want to do is put Peter in txtbox1, Jim in txtbox2 and so on (only a maximum of six). Does anyone know how to do this?

 

[Guest]

From the menu, choose Data, Text to columns, Delimited (using comma). This should work.

On 2002-03-07 11:36, waggy30 wrote:
Thanks. I couldn't get it to work. I don't know how to create arrays. However, i found a different way to solve problem 1.

To solve problem two, I was thinking about using some code to search for commas and then return the value i want.

For example, in one cell i have

Peter, Jim, Bob, Fred, Geoff, Jack


What I want to do is put Peter in txtbox1, Jim in txtbox2 and so on (only a maximum of six). Does anyone know how to do this?
[/quote]

 

[waggy30]

thanks, i never knew about that

 

[Mark O'Brien]

Here's some code that will do number 2 as well.

<pre>
Public Sub ExtractNames()

Dim iLength As Integer
Dim sFullText As String
Dim iThisFoundComma As Integer
Dim iLastFoundComma As Integer
Dim iCommaCount As Integer
Dim i As Integer
Dim sArray() As String
Dim sChar As String

sFullText = Sheets("Sheet1").Range("A1").Value
iLength = Len(sFullText)



For i = 1 To iLength
sChar = Mid$(sFullText, i, 1)
If sChar = "," Then
iThisFoundComma = i
iCommaCount = iCommaCount + 1
ReDim Preserve sArray(iCommaCount)
sArray(iCommaCount) = Mid$(sFullText, (iLastFoundComma + 1), (i - iLastFoundComma - 1))
iLastFoundComma = i
End If
Next

Dim test As String
For i = 1 To iCommaCount
test = test & sArray(i) & vbNewLine
Next

MsgBox test

End Sub
</pre>