selecting txt before a, or return character?

[neowok]

i have the following code which gets the text from an autoshape oval an
puts it into a textbox

Worksheets("3.data").TextBox1.Text = _
Worksheets("2.pla
only").Shapes(Application.Caller).TextFrame.Characters.Text

my problem is some of these ovals have more than one bit of text.
basically they are reference numbers, they are mostly just one numbe
per oval, but some have for example 6675-6679, <carriage retur
here>SH-639 or even just 6675,<return here>TA06

and im trying to perform searches based on the text it gets out of th
ovals so i need a way of selecting the text from the start to eithe
the ',' character, the '-' character or a return/new line character
and putting that into the textbox rather than the current way o
sticking the entire lot of text into it.

ideally in situations where theres more than one reference in the ova
i would provide a quick way of choosing which one they want to us
after they click that oval but i think this may be too complicated.

any ideas

 

[Tom Ogilvy]

You could parse the information out of each, using a lot of conditional
code. Or you could divide them up into 3 or 4 categories that can be
handled identically. Write a parsing sub for each and assign it to each of
the appropriate ovals - either duplicating you current code or passing the
appropriate value to you modified current code to get the value in the
textbox.

as far as separating the text

sStr = Worksheets("2.plan only").Shapes(Application.Caller). _
TextFrame.Characters.Text
iloc = instr(sStr,vbNewLine)
sStr1 = Left(sStr,iloc-2)
sStr2 = Right(sStr,len(sStr)-iloc)
' Worksheets("3.data").TextBox1.Text = sStr1
now if this is for the first type with the hyphen

iloc = Instr(sStr1,"-")
sStr1a = Left(sStr1,iloc-1)
sStr1b = right(sStr1,len(sStr1)-iloc)
' Worksheets("3.data").TextBox1.Text = sStr1a
' or sStr1b after you ask the user.

 

[neowok]

Sub OvalClick()
Sheets("3.Data").Select
sStr = Worksheets("2.plan only").Shapes(Application.Caller). _
TextFrame.Characters.Text
iloc = InStr(sStr, vbNewLine)
**sstr1 = Left(sStr, iloc - 2)**
Worksheets("3.data").TextBox1.Text = sstr1
Sheet1.CommandButton2_Click
End Sub

thats sub im using currently which is activated when the user clicks a
oval, im getting an "invalid procedure call or argument" error at th
line which ive starred above when clicking on an oval.

theres also a type mismatch error on the 3rd line above if you try t
run the vbcode by clicking the > in the vb editor but ive been gettin
this every time when running it this way, yet it worked fine if yo
actually went and clicked on an oval (and egts past this to fail on th
starred line if you click on an oval) so im not too worried bout that
just seems a bit strange.

thank

 

[Tom Ogilvy]

sStr = "ab12 " & vbNewLine & " cd23 "
iloc = InStr(sStr, vbNewLine)
sstr1 = Left(sStr, iloc - 2)
? sstr1
ab12

Unless you assigned this to only ovals that contain two entries, then
perhaps that is the problem - but the above was tested in the immediate
window and that line works fine from a syntax perspective.

 

[neowok]

something strange is happening here, assigning a value to sstr as i
your example works fine, but as soon as i change it from assigning
value, to getting the value from the oval, i get the error i describe
above.

so code that works fine and goes and finds the 6515 value is:

Sub OvalClick()
Sheets("3.Data").Select
sStr = "6515" & vbNewLine & "6517"
iloc = InStr(sStr, vbNewLine)
sStr1 = Left(sStr, iloc - 1)
Worksheets("3.data").TextBox1.Text = sStr1
Sheet1.CommandButton2_Click
End Sub

but code that throws the above error when clicking on the oval whic
contains those values is:

Sub OvalClick()
Sheets("3.Data").Select
sStr = Worksheets("2.plan only").Shapes(Application.Caller). _
TextFrame.Characters.Text
iloc = InStr(sStr, vbNewLine)
sStr1 = Left(sStr, iloc - 1)
Worksheets("3.data").TextBox1.Text = sStr1
Sheet1.CommandButton2_Click
End Sub

which then throws "invalid procedure call or argument" on the sStr1
Left(sStr, iloc - 1) line.

hovwering the mouse over the items in that line when it throws th
error gives me sstr1 = empty, sstr = "6515[]6517" and iloc = 0

the [] in between the numbers is a little square box. interestingly i
i move the mouse over the "vbnewline" text just above this line, i
shows vbnewline = "[][]" i.e. TWO little square boxes rather than th
one which is between the text it grabbed out of the oval, perhaps thi
has something to do with it? it looks abit like vbnewline is
carriage returns and whats actually in the ovals is only one? if i pu
TWO returns in after the first number in the oval, then sstr does en
up with 2 [] in it so its 6515[][]6517, but still throws the same erro
so perhaps this isnt relevant after all?

any ideas

 

[neowok]

done some more testing and its now clear that the carriage return tha
is in the oval is not the same as a 'vbnewline' so basically its no
picking it up, which is whats causing the error.

there must be another way to detect a return character other tha
looking for vbnewline?

thank

 

[Keith Willshaw]

done some more testing and its now clear that the carriage return that
is in the oval is not the same as a 'vbnewline' so basically its not
picking it up, which is whats causing the error.

there must be another way to detect a return character other than
looking for vbnewline?

thanks

You can look for the specific character using its ascii code
typically 10 for CR or 13 for NewLine using the INSTR function

If Instr(0, SearchString, chr(10)) <>0 Then
msgbox "New Line Found"
Endif

Keith