Excel VBA: Inserting a Symbol

AL_XResearch
AL_XResearch used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
HI,

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

Open in new window

Regards

Author

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'.
Top Expert 2016

Commented:
Sorry afaik it's not possible
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Author

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

Top Expert 2016

Commented:
No because it's like a sendkeys it just says open it

Author

Commented:
Indeed. I want to add that 'insert this character' functionality into my VBA solution.

Author

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.
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
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
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

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

Author

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
Bill PrewIT / Software Engineering Consultant
Top Expert 2016
Commented:
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
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015
Commented:
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 ~
Top Expert 2014
Commented:
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
Top Expert 2014

Commented:
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.
Top Expert 2014

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

Top Expert 2014

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

Open in new window

Top Expert 2016

Commented:
it does not yet solve the problem of getting the character(s) inserted
Top Expert 2014

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial