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
LVL 5
rberkeConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
If you paste with "Keep source formatting" it works fine.
0
gowflowCommented:
Just to Elaborate on what MartinLiss rightfully pointed out. When you copy the first text box simply position your cursor on the destination textbox and right click you will see that you have 3 options to choose from the one that will keep original formatting is the one in the below pic circled in red.

Sometimes it is not that obvious to understand the options especially in these new Excel versions the paste special has so many icons that I hardly understand them I personally look for Paste Special and it opens the old known window that you can choose clearly what you want !!!

Keep Orig Formating
gowflow
0
Martin LissOlder than dirtCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rberkeConsultantAuthor Commented:
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.
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

Open in new window


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.
0
rberkeConsultantAuthor Commented:
oops. ignore the sentence that beings with "But, when I past them ......".  Sorry for the  typo.
0
gowflowCommented:
did you read MartinLiss Last comment ? I think he pointed to why bullet looks the same but actually are not.
gowflow
0
Martin LissOlder than dirtCommented:
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.
0
rberkeConsultantAuthor Commented:
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. )
0
rberkeConsultantAuthor Commented:
In my last post I explained how to create a bullet whose "font is hidden and frozen"  (i.e. a Type 1 bullet)

Today figured out how to create a bullet whose font is  "unhidden and unfrozen". (Which I will call a Type 3 bullet). I use windows  start key > and type "character map" and use that program to put the wingdings character 108 onto the clipboard.  I then paste it with formatting into Excel (or Word).  

When I select that kind of bullet, the ribbon shows it as a Wingding so the font is "unhidden". I can also  change the font to Times New Roman, so it is "unfrozen".  (the character turns into a lower case L.)

This excersize also demonstrates why Microsoft's Insert Symbol function uses a hidden and frozen font.  When I pasted the Character Map's bullet the insertion point retained the Wingding font. When they appeared as wingding symbols.  When I tried to fix it by changing the whole paragraph back to times new roman,  the bullet turned into an "l".  Microsoft correctly decided that was very annoying behavior. Making the symbol's font be hidden and frozen resolves that problem, and the average user doesn't even realize that they are inserting Wingdings.

I am going to close this problem, but i still hope somebody can figure out how to create a "hidden and unfrozen" bullet (which is the weird "type 2" bullet that caused all these troubles.)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rberkeConsultantAuthor Commented:
The experts got me going in the right direction, but my final answer contains some interesting things that they did not mention.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.