Link to home
Create AccountLog in
Avatar of AL_XResearch
AL_XResearchFlag for United Kingdom of Great Britain and Northern Ireland

asked on

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.
SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of AL_XResearch

ASKER

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'.
Avatar of Rgonzo1971
Rgonzo1971

Sorry afaik it's not possible
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

No because it's like a sendkeys it just says open it
Indeed. I want to add that 'insert this character' functionality into my VBA solution.
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.
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
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
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
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ASKER CERTIFIED SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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.
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

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

Open in new window

it does not yet solve the problem of getting the character(s) inserted
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.