View Full Version : Insert Symbol made easy... a toolbar?

01-14-2008, 03:05 PM
We use certain math symbols in our cells all the time for documentation of neighboring cells. Using the Insert|Symbol...| then finding the needed symbol can be tedious.
I'd like to shortcut the process with user defined toolbar or flyout from an existing UD toolbar. The UDT would only show the symbols that we use most often and would work in the same fasion as the Insert|Symbol does.
For example the user would be typing in the cell "xyz" then click the toolbar and the greater than equal to symbol and that symbol would be inserted after the "z" - "xyz≥"

Is there an easier way then using SendKeys? (because this doesn't work anyway...)

Application.SendKeys ("%IS{TAB}{TAB}2265{TAB}~~")
In addition, the above cannot be used on the fly to insert into a line of active text.

01-15-2008, 08:13 PM
One thing to try to see if you like it is the Tools, Autocorrect capability.

I tried setting just left and right arrows using the macro below, and I used `l and `r for the left and right arrow symbols: 2190 and 2192.

Then entered data like


came out as


(using the real symbols :) )

Sub SetAutocorrects()

On Error Resume Next
Application.AutoCorrect.DeleteReplacement What:="`r"
Application.AutoCorrect.DeleteReplacement What:="`l"
On Error GoTo 0

'right arrow - I used the grave(?) to the left of the 'one' key, top row + r and l
' `l and `r

'InsertSymbol give you thehex Unicode value (e.g. 2190) for the symbol
'and I used HEX2DEC from the Analysis Tool Pack to do a one time conversion
'but any programming calculator could do it

Application.AutoCorrect.AddReplacement What:="`r", Replacement:=ChrW(8594) 'hex 2192
Application.AutoCorrect.AddReplacement What:="`l", Replacement:=ChrW(8592) 'hex 2190
End Sub


01-16-2008, 07:55 AM
Thanks Paul,

That works for converting, or autocorrecting items if you know the 'code' of `l or `r. In my case, the user would need to know 20 or so of these code. That would mean I would have to educated each user which is not practical or possible.

I created a macro attached to a button that used the sendkeys to send " 'l " but it only works if focus in not already in a cell. If the user is already typing in a cell and clicked on the macro button, nothing happens.

How does the Insert Symbol place a symbol into a cell that has been activated by being typed in?

Programatically, does anyone know how the Insert Symbol works?

01-16-2008, 05:01 PM
You could create a bunch of these ...

Sub LeftArrow()
AddSymbol (8592)
End Sub

Sub RightArrow()
AddSymbol (8590)
End Sub

Private Sub AddSymbol(iValue As Long)
Dim r As Range
If Not TypeOf Selection Is Range Then Exit Sub
For Each r In Selection.Cells
r.Value = r.Value & ChrW(iValue)
End Sub

and tie each of them into a button on a commandbar or menu

What they do is append a symbol to the end of the selection cell(s)

One problem is that editing, inserting, editing requires some mousing around

I'd consider using both .. maybe labeling the button with "L. Arrow (`l)" so that your users would have the option of doing it either way.


01-17-2008, 04:07 AM
Personally I would create a new user form, put a label on it and put a symbol into the caption.
(You could populate the form with however many of these you wanted on the 'initialise' event and simply incrament the symbol number by 1)

In the 'on click' event I would insert something like...
cell.value = cell.value & label1.caption

01-17-2008, 04:07 AM

01-17-2008, 12:07 PM
Thank. These are workable solutions. I ended up using a combination of both Paul's AutoCorrect and using a UserForm.

The problem turns out to be not the creation of the symbols but the use of VBA Macros while Excel is in Edit mode. No one has been able to mimic the Insert | Symbols... built in function that IS usable in Edit mode.

Does anyone know how the Insert Symbol dialog box works that allows it to function while Excel is in Edit mode?