View Full Version : [SOLVED:] Split listbox text
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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.