Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting txt before a, or return character?
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.Charac ters.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 hereSH-639 or even just 6675,<return hereTA06 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 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting txt before a, or return character?
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. -- Regards, Tom Ogilvy "neowok " wrote in message ... i have the following code which gets the text from an autoshape oval and puts it into a textbox Worksheets("3.data").TextBox1.Text = _ Worksheets("2.plan only").Shapes(Application.Caller).TextFrame.Charac ters.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 number per oval, but some have for example 6675-6679, <carriage return hereSH-639 or even just 6675,<return hereTA06 and im trying to perform searches based on the text it gets out of the ovals so i need a way of selecting the text from the start to either the ',' character, the '-' character or a return/new line character, and putting that into the textbox rather than the current way of sticking the entire lot of text into it. ideally in situations where theres more than one reference in the oval i would provide a quick way of choosing which one they want to use after they click that oval but i think this may be too complicated. any ideas? --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting txt before a, or return character?
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 -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting txt before a, or return character?
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. -- Regards, Tom Ogilvy "neowok " wrote in message ... 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 an oval, im getting an "invalid procedure call or argument" error at the line which ive starred above when clicking on an oval. theres also a type mismatch error on the 3rd line above if you try to run the vbcode by clicking the in the vb editor but ive been getting this every time when running it this way, yet it worked fine if you actually went and clicked on an oval (and egts past this to fail on the starred line if you click on an oval) so im not too worried bout that, just seems a bit strange. thanks --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting txt before a, or return character?
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 -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting txt before a, or return character?
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 -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting txt before a, or return character?
"neowok " wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return position of 2nd, 3rd, ect occurrence of a character in a st | Excel Discussion (Misc queries) | |||
Printer return character | Excel Discussion (Misc queries) | |||
SELECTING FROM A DROP DOWN LIST BY TYPING FIRST CHARACTER | New Users to Excel | |||
Using replace to get rid of paragraph return character | Excel Discussion (Misc queries) | |||
Return the 6th thru the nth character? | Excel Worksheet Functions |