Set Accelerator for programmatically added button

Question

I can't figure out what I'm doing wrong here. I added a button to an Excel Sheet programmatically. I am trying to assign an accelerator key, but it does not get assigned. The relevant code is:

Sub addPrint(sht, Optional fromLeft, Optional fromTop)
If IsMissing(fromLeft) Then fromLeft = 180
If IsMissing(fromTop) Then fromTop = 10
    Set printbut = sht.Buttons.Add(fromLeft, fromTop, 50, 20)
    printbut.Name = "PrintButton"
    printbut.OnAction = "Sheet4.printButton"
    printbut.Characters.Text = "Print/PDF"
    printbut.Accelerator = "P"
End Sub

The 'P' does not get underlined and Alt-P does nothing.

Im not sure if [this](http://www.vbforums.com/showthread.php?524674-RESOLVED-Excel-VBA-Button-Shortcut-Keys) will help but I find it amusing that there are post from @SiddharthRout from 2008 regarding this same subject — mrbungle, Oct 02 '14 at 20:01
See post 6 and 7 in the link given by @mrbungle. The problem is that you are creating a form control and not an activeX button. form control doesn't have an `.Accelerator` property. — Siddharth Rout, Oct 02 '14 at 21:03
That has also been answered by me and several others in Stackoverflow. Do a search ;) — Siddharth Rout, Oct 02 '14 at 21:05
Thank you. I found some answers. http://stackoverflow.com/questions/3549586/how-to-create-a-dynamic-button-in-excel — Abe Gold, Oct 02 '14 at 21:11
You may want to see [THIS](http://stackoverflow.com/questions/10633387/programatically-inserting-click-event-code-for-dynamically-generated-label-not-w) as well :) — Siddharth Rout, Oct 02 '14 at 21:22

score 0 · Answer 1 · answered Oct 03 '14 at 08:47

This is the way to add an ActiveX-Button:

Sub addActiveXCommandButton(sht As Worksheet, Optional left As Single = 100, Optional top As Single = 100)

    Dim btn As OLEObject

    '
    'create Button
    '
    Set btn = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, _
                    DisplayAsIcon:=False, left:=left, top:=top, _
                    Width:=105.75, Height:=36)

    Debug.Print TypeName(btn)           ' this returns OLEObject as a wrapper of the CommandButton
    Debug.Print TypeName(btn.Object)    ' this returns CommandButton - the activeX-Object

    '
    ' access the CommandButton-Object and set the Accelerator value
    '
    btn.Object.Accelerator = "B"

End Sub

However, I am not certain, that the Accelerator Button may be accessed. On testing, the Accelerator Button could bot be accessed using the Alt-key. I use a solution with a button and an application.onKey-definition that both access the same procedure.

Thank you. I ended up following an anwer given on another question and its working. See comments below. — Abe Gold, Oct 03 '14 at 13:02
http://stackoverflow.com/questions/3549586/how-to-create-a-dynamic-button-in-excel. Then you'll probably want to look at http://stackoverflow.com/questions/10633387/programatically-inserting-click-event-code-for-dynamically-generated-label-not-w — Abe Gold, Oct 07 '14 at 19:34

Set Accelerator for programmatically added button

1 Answers1