Robert Berke
asked on
Copying contents of textbox to another textbox screws up in Excel 2010
This drove me crazy. One Excel Textbox has 4 bulleted paragraphs. When I copy the contents into another textbox, 2 of the bullets get clobbered. Open the attachment and try it yourself.
I worked around the problem by copying the entire box, but I thing this is might weird.
By the way, the text originated in a word document which might have something to do with this screwy stuff.
So, the problem is "solved" but I wonder if anyone really understands what the heck is going on.
rberke
Excel-Weird-textboxes.xlsx
I worked around the problem by copying the entire box, but I thing this is might weird.
By the way, the text originated in a word document which might have something to do with this screwy stuff.
So, the problem is "solved" but I wonder if anyone really understands what the heck is going on.
rberke
Excel-Weird-textboxes.xlsx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And part of the problem is that Excel doesn't recognize them as bullets even though visually they look like them. Rather the Type 1 "bullet" is being interpreted as Ascii character 63 which is the question mark, and the Type 2 "bullet" is being interpreted as Ascii character 108 which is lower case l.
ASKER
You guys have advanced my knowledge a little bit. I now know that "keep source formatting (alt hvk)" is helpful, but I still don't know why "used destination theme (alt hvh)" does not work.
I also notice that if I highlight the text in Text Box 2(the source), and use ctrl space to remove formatting, the type1 bullets are unaffected, but the type 2 bullets revert to L.
I think unicode characters are getting involved here, but I don't understand exactly how.
I copy the contents and pasted them with formatting into MS Word. Then I ran the following macro on each character.
I was expecting type 1 bullets to show Asc 63, but I was wrong. It shows this.
Asc:40
Chr(Asc):(
Font: Wingdings
Char number -3988
I was expecting Type 2 bullets to show Asc = 108 which is lower case L. I was wrong it actually looks like this.
Asc:63
Chr(Asc):?
Font: Wingdings
Char number -3988
But, when I paste them onto a text box using the destination them, the type 2 bullets get zapped.
Can anybody give me a 50 word or less explanation to why both types of bullets look identical but are really different.
If not, I will give up and accept "use paste special and keep formatting" as the "correct" answer.
I also notice that if I highlight the text in Text Box 2(the source), and use ctrl space to remove formatting, the type1 bullets are unaffected, but the type 2 bullets revert to L.
I think unicode characters are getting involved here, but I don't understand exactly how.
I copy the contents and pasted them with formatting into MS Word. Then I ran the following macro on each character.
Sub GetCharNoAndFont()
With Dialogs(wdDialogInsertSymbol)
MsgBox "Asc:" & Asc(Selection) _
& vbCrLf & "Chr(Asc):" & Chr(Asc(Selection)) _
& vbCrLf & "Font: " & .Font _
& vbCrLf & "Char number " & .CharNum
End With
End Sub
I was expecting type 1 bullets to show Asc 63, but I was wrong. It shows this.
Asc:40
Chr(Asc):(
Font: Wingdings
Char number -3988
I was expecting Type 2 bullets to show Asc = 108 which is lower case L. I was wrong it actually looks like this.
Asc:63
Chr(Asc):?
Font: Wingdings
Char number -3988
But, when I paste them onto a text box using the destination them, the type 2 bullets get zapped.
Can anybody give me a 50 word or less explanation to why both types of bullets look identical but are really different.
If not, I will give up and accept "use paste special and keep formatting" as the "correct" answer.
ASKER
oops. ignore the sentence that beings with "But, when I past them ......". Sorry for the typo.
did you read MartinLiss Last comment ? I think he pointed to why bullet looks the same but actually are not.
gowflow
gowflow
I'm not sure that you will find this article completely satisfying but it does I believe explain why the weird things are happening in Excel.
ASKER
That article helped a little.
It appears that both the type 1 bullet and the type 2 bullet are both based upon the 108th character in the wingding font.
But Excel (and Word) have applied special formatting for the type 1 bullet that causes the character's font name to be both hidden and frozen.
Hidden font name means: If I select just the bullet, the ribbon still displays Times New Roman, even though it is really a Wingding.
Frozen font name means: I cannot change it from Wingding to Times New Roman.
By contrast, the Type 2 bullet is hidden and unfrozen.
I think this "Frozen font" concept is new. I am pretty sure office 2003 allowed me to convert an arbitrary phrase into wingdings if I wanted.
I am about ready to close the problem. I'll just wait for any final comments from the participants.
But, there is one thing I still don't understand -- how was the type 2 bullet created? ( If I click in an empty text box the use Insert > Wingdings and select the Bullet it creates a Type 1. )
It appears that both the type 1 bullet and the type 2 bullet are both based upon the 108th character in the wingding font.
But Excel (and Word) have applied special formatting for the type 1 bullet that causes the character's font name to be both hidden and frozen.
Hidden font name means: If I select just the bullet, the ribbon still displays Times New Roman, even though it is really a Wingding.
Frozen font name means: I cannot change it from Wingding to Times New Roman.
By contrast, the Type 2 bullet is hidden and unfrozen.
I think this "Frozen font" concept is new. I am pretty sure office 2003 allowed me to convert an arbitrary phrase into wingdings if I wanted.
I am about ready to close the problem. I'll just wait for any final comments from the participants.
But, there is one thing I still don't understand -- how was the type 2 bullet created? ( If I click in an empty text box the use Insert > Wingdings and select the Bullet it creates a Type 1. )
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The experts got me going in the right direction, but my final answer contains some interesting things that they did not mention.