Combo Box partial value

[Steve C]

I have a combo box on a userform in which after the user selects their choice
and clicks OK, I want to separate out the first part of the selection from
the second part and distribute these parts (via bookmarks) into different
areas of the document.

For example, if the combo box choices were "9A Metal Railings" and "16B
Masonry Material," I only want "9A" or "16B" inserted into one area of the
document, while its corresponding description is placed somewhere else.

My challenge is that 9A is two characters (followed by a space) and 16B is
three characters. I'm thinking that my code should look for the first space
that occurs from the left side of the description and then store whatever is
left of that space in one variable, and whatever is to the right of that in
another variable. I could then set the bookmark text equal to the
appropriate values of those variables.

How do I write code to find the position of that first space from the left?
Thanks!

Steve C

 

[Jay Freedman]

Hi Steve,

The InStr function finds the position of a substring (in this case, the
space character) within a larger string. For instance, if the variable
PartName contains the string "9A Metal Railings", then

Dim nSpace As Integer
nSpace = InStr(PartName, " ")

would return 3 because the space is the third character. Then the two parts
of the string would be retrieved by

Dim PartNumber As String, PartDesc As String
PartNumber = Left(PartName, nSpace - 1) ' gets "9A"
PartDesc = Right(PartName, nSpace + 1, Len(PartName) - nSpace)
' gets "Metal Railings"

 

[Steve C]

Thank you, Jay. I will give it a try!

Steve C

Hi Steve,

The InStr function finds the position of a substring (in this case, the
space character) within a larger string. For instance, if the variable
PartName contains the string "9A Metal Railings", then

Dim nSpace As Integer
nSpace = InStr(PartName, " ")

would return 3 because the space is the third character. Then the two parts
of the string would be retrieved by

Dim PartNumber As String, PartDesc As String
PartNumber = Left(PartName, nSpace - 1) ' gets "9A"
PartDesc = Right(PartName, nSpace + 1, Len(PartName) - nSpace)
' gets "Metal Railings"

 

[Perry]

Provided a space seperates the martialcode from the description:
=start snip
Dim sCode As String
Dim sDescr As String

With Me.ComboBox
sCode = Split(.List(.ListIndex), Space(1))(0)
sDescr = Split(.List(.ListIndex), Space(1))(1)
End With

MsgBox "My code: " & sCode & vbCr & _
"Corresponding descr: " & sDescr
= end snip

Expl: sCode gets the left part off the delimiter returned by the Split()
function
sDescr returns right part off the delimiter.

Krgrds,
Perry

Follow below URL to get you info on Split(); word wrap can below URL in two
lines, it is a complete address...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odeopg/html
/deconthesplitfunction.asp

 

[Jay Freedman]

Hi Perry,

I thought about using Split(), but it doesn't gain much for the 'code'
part and it doesn't properly handle the 'description' part. For the
examples Steve gave, your code only returns "Metal" as the description
for "9A Metal Railings", and "Masonry" as the description for "16B
Masonry Material". You would have to use Join() to put back together
all the pieces except the first.

 

[Perry]

part and it doesn't properly handle the 'description' part. For the

examples Steve gave, your code only returns "Metal" as the description

Not if you use:

sDescr = Split(.List(.ListIndex), Space(1), 2)(1)
Note: Third parameter to Split()

Krgrds,
Perry

 

[Jay Freedman]

Ahh, good one. I forgot about the Limit parameter.

Jay