short key to invoke command button in userform

[amit]

hi - I've got a userfomr that has a "Save & Close" command button which saves
the user entry back to a database.....is ther a way to invoke the command
button by using ctrl + s ......instead of having to click the command
button....i would like to allow for this to work both ways....

thanks
Amit.

 

[Norman Jones]

Hi Amit,

See the Acceleror property in VBA help.

In the Userform's module try something like:


=========>>
Private Sub UserForm_Initialize()
Const sAccelerator As String = "s"

Me.CommandButton1.Accelerator = sAccelerator

End Sub
<<=========

Note that this acelerator key would be invoked
with the Alt-s key combination.

 

[Norman Jones]

See the Acceleror property in VBA help.

====>>

See the Accelerator property in VBA help.

 

[atpgroups]

hi - I've got a userfomr that has a "Save & Close" command button which saves
the user entry back to a database.....is ther a way to invoke the command
button by using ctrl + s

See http://msdn.microsoft.com/en-us/library/aa195807(office.11).aspx

You need a line somewhere whch says
Application.Onkey "^s", "MySaveCloseRoutine"

Either put in in the form_activate event code and then disable it
again in form_deactivate if you only want it to be active with the
form open, or put it in Workbook_Open and deactivate in
Workbook_BeforeClose if you want it to work all the time.

I have a (stupidly) huge VBA Macro which has subverted all the Excel
menu commands and keyboard shortcuts in this way so that users can
exchange data files without sending out a copy of the macro/
application with each one. This sounds not dissimilar to what you are
trying to do.

 

[Norman Jones]

Hi AtpGroups,

I suspect that it was your intention to respond
to Armit rather than to me.

Since, however you have appended your
response to my post, see my reply to Armit
and note thata CommandButton has an
Accelerator property.

 

[amit]

thank Norman........this worked for me....

regards,
Amit