PDA

View Full Version : Solved: Split listbox text



Dave
07-08-2008, 11:17 AM
Can't seem to get the syntax right for using the split function. The listbox text is as follows: Field1 WHEAT (there are 5 spaces between the 2 words). I would like to be able to return either Field1 or WHEAT. Any help would be appreciated. Dave

mdmackillop
07-08-2008, 11:22 AM
Will the spaces be consistent?

Sub test()
txt = "Field1 Wheat"
tmp = Split(txt, " ")
MsgBox tmp(0) & vbCr & tmp(1)
End Sub

mdmackillop
07-08-2008, 11:32 AM
More flexible

Sub test2()
Dim Txt As String, Spaces as long, Tmp

Txt = "Field1 Wheat"
spaces = InStrRev(Txt, Chr(32), -1) - InStr(1, Txt, Chr(32)) + 1
Tmp = Split(Txt, String(spaces, " "))
MsgBox Tmp(0) & vbCr & Tmp(1)
End Sub

Dave
07-08-2008, 11:48 AM
Thanks md for the help and the extra flexibility... it is however always 5 spaces. Your example works perfect but... in the real for instance XL throws an expected array compile error (it highlights LB1str(0)below)? Dave
Dim LB1str As String
LB1str = Split(ListBox1.Text, " ")
Sheets("Sheet1").Range("Fs" & ListBox2.ListIndex + 1).Value = _
TextBox2.Text & " " & LB1str(0) & "(" & TextBox4.Text & ")"

mdmackillop
07-08-2008, 02:59 PM
Split returns an array, not a string so either
Dim LB1str As Variant
'or
Dim LB1str

Dave
07-08-2008, 04:02 PM
The work aound goes like this. Thanks again for the help. Dave

Sub test()
'place listbox on sheet1 for testing
Dim LB1str As Variant, FldStr As String, GrnStr As String
If Sheet1.ListBox1.ListIndex = -1 Then
Sheet1.ListBox1.Clear
Sheet1.ListBox1.AddItem "Field1 WHEAT"
MsgBox "Make selection"
Else
LB1str = Split(Sheet1.ListBox1.Text, " ")
FldStr = LB1str(0)
GrnStr = LB1str(1)
'Sheets("Sheet1").Range("Fs" & ListBox2.ListIndex + 1).Value = _
TextBox2.Text & " " & FldStr & "(" & TextBox4.Text & ")"
MsgBox "Field: " & FldStr & " Grain: " & GrnStr
End If
Sheet1.ListBox1.ListIndex = -1
End Sub