Excel VBA: Inserting a Symbol

In Excel 2013 from the 'Insert' tab you can select 'Symbol' to 'insert a symbol. This displays a large dialog and allows you to select a dialog.

Does anyone know how to trigger this from VBA ?  I has assumed it was one of the 'Application.Dialogs' screens but I can't find it.
LVL 3
AL_XResearchAsked:
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.

Rgonzo1971Commented:
HI,

pls try
Sub show_symbol_dlg()
Application.CommandBars.FindControl(ID:=308).Execute
End Sub

Open in new window

Regards
1
AL_XResearchAuthor Commented:
Hi Rgonzo1971,

Thanks for that. It does indeed do what I want -HOWEVER; it only shows the dialog, it doesn't allow me to return a character.

Is there anything less cryptic than 'CommandBars.FindControl(ID:=308)' ? Say for example of the style 'strTest = Application.insertSymbol'.
0
Rgonzo1971Commented:
Sorry afaik it's not possible
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.

AL_XResearchAuthor Commented:
Seems strange that you can't return a character from below command - even if its not very pleasing to the eye !
Sub show_symbol_dlg()
Application.CommandBars.FindControl(ID:=308).Execute
End Sub

Open in new window

0
Rgonzo1971Commented:
No because it's like a sendkeys it just says open it
0
AL_XResearchAuthor Commented:
Indeed. I want to add that 'insert this character' functionality into my VBA solution.
0
AL_XResearchAuthor Commented:
Is there any way to call the Windows Accessory 'Character map' to allow the selection of a character ?

Obviously this would have to be returned to VBA for inclusion.
0
Bill PrewCommented:
Well, easy enough to launch that, just RUN "%windir%\system32\charmap.exe".

And wait for it to end.

The only problem with it is that the user has to decide to do the COPY button while there, so that when it returns you can get the character out of the clipboard.  I don't think there is anyway to know if they did the COPY, to know if the clipboard is the symbol(s), or leftovers from a prior copy.


»bp
0
Bill PrewCommented:
What's your application / use case, perhaps building your own small form to do this would end up being easier and more friendly.  Either of these dialogs are somewhat busy and you can change characters sets etc, which could change the effect unless you are using that info as well...


»bp
0
AL_XResearchAuthor Commented:
Bill: the tool is basically a text editor and I want the user to be able to insert accented symbols and or bullet points. That is why I ideally want to the use the Excel 'Insert symbol' - especially as there is a 'recently used' list of characters
0
Bill PrewCommented:
Okay, using charmap.exe and putting the burden on them to copy in the dialog and then paste when they get back might be the 'easiest' alternative, without creating your own version.  No history there though I don't think.


»bp
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
aside from Bill's suggestion to launch the character map and copy (code could then paste), if you are perhaps wanting to know how to insert specific characters that can be chosen from this dialog box, as opposed to triggering the dialog box ...

you can insert a character into the current cell using  worksheet functions CHAR and UNICHAR ... for instance, try CHAR(65) or UNICHAR(9600) to get 'A'  or the upper bar character.

The VBA equivalents are Chr and ChrW

If you are writing a text editor, then to get the choices, you'd have to build your own list of choices they might want to pick -- easy enough to generate the characters ~
0
aikimarkCommented:
Chuck Pearson wrote his own add-in for doing this.  I don't think it is as sophisticated as the standard dialog, it might get you to MVP
http://www.cpearson.com/excel/chars.htm
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
aikimarkCommented:
If you are in Word, you can use
application.Dialogs(wdDialogInsertSymbol).Show

Open in new window

Where wdDialogInsertSymbol = 162

With that in mind, you should be able to instantiate a word.automation object and use a statement like this to display the dialog.
0
aikimarkCommented:
Although the dialog shows in Excel with this command, it doesn't seem useful.  You'd need to look at the contents of the current cell after the dialog goes away.
Application.CommandBars.FindControl(ID:=308).Execute

Open in new window

0
aikimarkCommented:
Ah Ha!  The secret value of the unpublished intrinsic constant in Excel is 771.
Application.Dialogs(771).Show

Open in new window

0
Rgonzo1971Commented:
it does not yet solve the problem of getting the character(s) inserted
0
aikimarkCommented:
There are two problems using this dialog in Excel
1. It automatically invokes an edit operation on the activecell.  Code is suspended until the dialog window is closed.
2. Unlike the fileselect dialogs, it doesn't retain a value.

My earlier suggestion, using a Word automation object, should allow you to capture the inserted characters in a non-visible document window.
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 Office

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.