Difference between Selection.Text and Shape.AlternativeText?

[Steven M (remove cola to reply)]

I have an outside application that reads Excel files, but there is
something causing an error. I tracked it to a text box that contains
something that is not exactly text. I could probably get rid of the
text box and puts its contents directly into the underlying cells, but
I would rather modify the text box using VBA. (The first file has 35
worksheets, each with several text boxes and there might be more
later.)

The code I have written cycles through all objects on all worksheets.
I can access the text in a particular text box two different ways:

Worksheets(j).Shapes(i).Select
myText = Worksheets(j).Shapes(i).AlternativeText

thisText = Selection.Text

However, when I run it, they give two different values:

myText = "VALVES"
thisText = "¶ VALVES"

The character that I give as a ¶ is actually ASCII code 10, or
NewLine.

I want to cycle through all the boxes and delete any leading space and
other characters that are not visible characters from each box.

However, he only way that I see to assign text to the object when I
select it this way is with the AlternativeText property. But that
method doesn't get rid of the NewLine.

And this statement gives an error:

Selection.Text = myText

Run-time error '1004':
Unable to set the Text property of the Range class.

Any ideas?

 

[Jim Rech]

There are two kinds of texts boxes in Excel and they are quite different
unfortunately. Are yours from the Control Toolbox or the Drawing toolbar?

--
Jim Rech
Excel MVP
message |I have an outside application that reads Excel files, but there is
| something causing an error. I tracked it to a text box that contains
| something that is not exactly text. I could probably get rid of the
| text box and puts its contents directly into the underlying cells, but
| I would rather modify the text box using VBA. (The first file has 35
| worksheets, each with several text boxes and there might be more
| later.)
|
| The code I have written cycles through all objects on all worksheets.
| I can access the text in a particular text box two different ways:
|
| Worksheets(j).Shapes(i).Select
| myText = Worksheets(j).Shapes(i).AlternativeText
|
| thisText = Selection.Text
|
| However, when I run it, they give two different values:
|
| myText = "VALVES"
| thisText = "¶ VALVES"
|
| The character that I give as a ¶ is actually ASCII code 10, or
| NewLine.
|
| I want to cycle through all the boxes and delete any leading space and
| other characters that are not visible characters from each box.
|
| However, he only way that I see to assign text to the object when I
| select it this way is with the AlternativeText property. But that
| method doesn't get rid of the NewLine.
|
| And this statement gives an error:
|
| Selection.Text = myText
|
| Run-time error '1004':
| Unable to set the Text property of the Range class.
|
| Any ideas?
|
|
|
| --
| Steve M - (e-mail address removed) (remove dirt for reply)
|
| "I go online sometimes, but... everyone's spelling is really bad.
| It's depressing." -- Tara, "Buffy the Vampire Slayer"

 

[Steven M (remove cola to reply)]

Jim,

I'm not sure, because the document was created by someone else. But I
think it was from the Drawing toolbar.

The creator wasn't the most sophisticated Excel user. For example:
the file does not contain a single fomula. It is being used only to
present tables of information. Tables in Word would have been a
better choice. There are several places where they didn't use the
best formatting available in Excel.

It is definitely not obvious to this first-time user how to obtain a
text box using the Control Toolbox.

Also, I created a couple of boxes using the Drawing toolbar, and the
attributes are similar to those in my big drawing.

So the Drawing toolbar is the more likely candidate.

Now what?

 

[Jim Rech]

With Drawing toolbar text boxes I find this clears out control characters
and leading/trailing spaces:

Sub a()
Dim Obj As Object
For Each Obj In ActiveSheet.TextBoxes
Obj.Text = Application.Clean(Trim(Obj.Text))
Next
End Sub


--
Jim Rech
Excel MVP
message | Jim,
|
| I'm not sure, because the document was created by someone else. But I
| think it was from the Drawing toolbar.
|
| The creator wasn't the most sophisticated Excel user. For example:
| the file does not contain a single fomula. It is being used only to
| present tables of information. Tables in Word would have been a
| better choice. There are several places where they didn't use the
| best formatting available in Excel.
|
| It is definitely not obvious to this first-time user how to obtain a
| text box using the Control Toolbox.
|
| Also, I created a couple of boxes using the Drawing toolbar, and the
| attributes are similar to those in my big drawing.
|
| So the Drawing toolbar is the more likely candidate.
|
| Now what?
|
|
|
|
|
| On Wed, 2 Jun 2004 09:55:45 -0400, "Jim Rech" <[email protected]>
| wrote:
|
| >There are two kinds of texts boxes in Excel and they are quite different
| >unfortunately. Are yours from the Control Toolbox or the Drawing
toolbar?
| >
| >--
| >Jim Rech
| >Excel MVP
| >message | >|I have an outside application that reads Excel files, but there is
| >| something causing an error. I tracked it to a text box that contains
| >| something that is not exactly text. I could probably get rid of the
| >| text box and puts its contents directly into the underlying cells, but
| >| I would rather modify the text box using VBA. (The first file has 35
| >| worksheets, each with several text boxes and there might be more
| >| later.)
| >|
| >| The code I have written cycles through all objects on all worksheets.
| >| I can access the text in a particular text box two different ways:
| >|
| >| Worksheets(j).Shapes(i).Select
| >| myText = Worksheets(j).Shapes(i).AlternativeText
| >|
| >| thisText = Selection.Text
| >|
| >| However, when I run it, they give two different values:
| >|
| >| myText = "VALVES"
| >| thisText = "¶ VALVES"
| >|
| >| The character that I give as a ¶ is actually ASCII code 10, or
| >| NewLine.
| >|
| >| I want to cycle through all the boxes and delete any leading space and
| >| other characters that are not visible characters from each box.
| >|
| >| However, he only way that I see to assign text to the object when I
| >| select it this way is with the AlternativeText property. But that
| >| method doesn't get rid of the NewLine.
| >|
| >| And this statement gives an error:
| >|
| >| Selection.Text = myText
| >|
| >| Run-time error '1004':
| >| Unable to set the Text property of the Range class.
| >|
| >| Any ideas?
| >|
| >|
| >|
| >| --
| >| Steve M - (e-mail address removed) (remove dirt for reply)
| >|
| >| "I go online sometimes, but... everyone's spelling is really bad.
| >| It's depressing." -- Tara, "Buffy the Vampire Slayer"
| >
|
|
| --
| Steve M - (e-mail address removed) (remove dirt for reply)
|
| "I go online sometimes, but... everyone's spelling is really bad.
| It's depressing." -- Tara, "Buffy the Vampire Slayer"

 

[Steven M (remove cola to reply)]

Jim,

Thanks. I made a small change, to get all the boxes in all the sheets
at one time (there are 35 sheet in one file):


Sub CleanTextBoxes()
Dim Obj As Object, Sht
For Each Sht In Worksheets
For Each Obj In Sht.TextBoxes
Obj.Text = Application.Clean(Trim(Obj.Text))
Next
Next
End Sub


Next question: What is the data type of "Sht"? There is no "Sheet"
data type, and VBA offered me only Sheet1 and Sheet2 but neither one
of them worked.

Thanks again.

Steven

 

[Jim Rech]

You would dim it as type "Worksheet".

--
Jim Rech
Excel MVP

message | Jim,
|
| Thanks. I made a small change, to get all the boxes in all the sheets
| at one time (there are 35 sheet in one file):
|
|
| Sub CleanTextBoxes()
| Dim Obj As Object, Sht
| For Each Sht In Worksheets
| For Each Obj In Sht.TextBoxes
| Obj.Text = Application.Clean(Trim(Obj.Text))
| Next
| Next
| End Sub
|
|
| Next question: What is the data type of "Sht"? There is no "Sheet"
| data type, and VBA offered me only Sheet1 and Sheet2 but neither one
| of them worked.
|
| Thanks again.
|
| Steven
|
|
|
|
| On Wed, 2 Jun 2004 11:39:48 -0400, "Jim Rech" <[email protected]>
| wrote:
|
| >With Drawing toolbar text boxes I find this clears out control characters
| >and leading/trailing spaces:
| >
| >Sub a()
| > Dim Obj As Object
| > For Each Obj In ActiveSheet.TextBoxes
| > Obj.Text = Application.Clean(Trim(Obj.Text))
| > Next
| >End Sub
|
|
| --
| Steve M - (e-mail address removed) (remove dirt for reply)
|
| "I go online sometimes, but... everyone's spelling is really bad.
| It's depressing." -- Tara, "Buffy the Vampire Slayer"