Solved

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

Posted on 2014-09-15
10
157 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 45

Assisted Solution

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

Assisted Solution

by:gowflow
gowflow earned 250 total points
Comment Utility
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 45

Expert Comment

by:Martin Liss
Comment Utility
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
 
LVL 5

Author Comment

by:rberke
Comment Utility
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
Comment Utility
oops. ignore the sentence that beings with "But, when I past them ......".  Sorry for the  typo.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 29

Expert Comment

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

Expert Comment

by:Martin Liss
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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 article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

728 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now