Accelerator

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
Hello,

Is there a way to use accelerator to a commandbutton, without recieving focus? I have tried the takefocusonclick=false but only works when clicking on control, not when using accelerator key.

Thank you
Kostas
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Hi Kostas,

I assume you're talking about a Commandbutton on a userform?

Normally you create an accelerator shortcut by placing an ampersand before the accelerator letter. For example: C&ancel as the caption for a button. This will appear as Cancel. When you press Alt + A in the form, you will trigger the Cancel button.

Denis
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
I'm talking about a userform's button, and yes I know how accelerator works, how to assing letters e.c.t. My problem is that when i click on commandbutton1 the focus remains at textbox1 as I want this to be, but when i strike alt+F (button's accelerator) focus moves to button.
The reason why i want activecontrol not to loose focus is the following code assigned to commandbutton1:
Code:
Dim wsh As Worksheet
Dim x, Clm As Range
Set wsh = Sheet1
If Me.ActiveControl.TabIndex = 0 Then Set Clm = wsh.Range("a7:a65536")
If Me.ActiveControl.TabIndex = 2 Then Set Clm = wsh.Range("b7:b65536")
If Me.ActiveControl.TabIndex = 5 Then Set Clm = wsh.Range("c7:c65536")
If Me.ActiveControl.TabIndex = 7 Then Set Clm = wsh.Range("d7:d65536")
If Me.ActiveControl.TabIndex = 9 Then Set Clm = wsh.Range("e7:e65536")
If Me.ActiveControl.TabIndex = 11 Then Set Clm = ws.Range("f7:f65536")
Set x = Clm.Find(Me.ActiveControl.Text, LookAt:=xlWhole)
If x Is Nothing Then
    MsgBox "No records found": Exit Sub
Else
Set x = Range("A" & x.Row)
    With x
           .Activate
           .Resize(, 26).Copy Destination:=Sheet1.Range("aa1")
    End With
End If

So if activecontrol isn't textbox1 (tabindex0) or textbox2(tabindex2) e.c.t. the code does not work.
Once in a similar matter an advice was given by Erik.Van.Geit to use a classmodule procedure so on exit event of a control a variant takes the (value? tabindex? name of control? or something else) but class moduling isn't my best part :(
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
OK, now I see what you're after.

Not tested, but I use this trick a lot in Access:
You could assign this code to the AfterUpdate event of each textbox
CommandButton1_Click

That should trigger the button's Click event without changing the focus.

Denis
 

ktab

Well-known Member
Joined
Apr 21, 2005
Messages
1,297
almost working for what i need; i'll give it a harder try (and to similar events) and let you know...
 

Forum statistics

Threads
1,141,098
Messages
5,704,319
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top