Solved

Copying contents of textbox to another textbox screws up in Excel 2010

Posted on 2014-09-15
10
166 Views
Last Modified: 2014-09-22
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
0
Comment
Question by:rberke
  • 5
  • 3
  • 2
10 Comments
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 250 total points
ID: 40324289
If you paste with "Keep source formatting" it works fine.
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 250 total points
ID: 40325783
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40325847
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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 5

Author Comment

by:rberke
ID: 40326501
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
 
LVL 5

Author Comment

by:rberke
ID: 40326504
oops. ignore the sentence that beings with "But, when I past them ......".  Sorry for the  typo.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40326640
did you read MartinLiss Last comment ? I think he pointed to why bullet looks the same but actually are not.
gowflow
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 40326686
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
 
LVL 5

Author Comment

by:rberke
ID: 40327331
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
 
LVL 5

Accepted Solution

by:
rberke earned 0 total points
ID: 40328488
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
 
LVL 5

Author Closing Comment

by:rberke
ID: 40336176
The experts got me going in the right direction, but my final answer contains some interesting things that they did not mention.
0

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question