Free Microsoft Excel 2013 Quick Reference

Enter Key to Trigger CommandButton Click Event

Hello. I am working with a large legacy file/program which has a lot of issues. Foremost, and unfortunately this cannot be changed, is that all of the controls were placed directly on the worksheets instead of on Userforms.

The actual code from the legacy file is excessively complicated, so I created this simple example. Sheet1 has one textbox and one command button. Sheet 2 is blank. I want to be able to click the command button or use the Enter key on the command button, to trigger the Click Event. Clicking works fine. When using Enter, if Sheet2.Activate is commented, everything works fine. If Sheet2.Activate is executed, then Excel crashes. Any ideas are greatly appreciated!

Thanks, Tom


	VB:
	
) 
    Dim bBackwards As Boolean 
    Select Case KeyCode 
         'Only look for tab, return, down arrow, up arrow
    Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp 
        Application.ScreenUpdating = False 
         'Do we need to go back to previous control
        bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp) 
        If bBackwards Then TextBox1.Activate Else CommandButton1.Activate 
        Application.ScreenUpdating = True 
    End Select 
End Sub 
Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) 
    Dim bBackwards, bForwards As Boolean 
    Select Case KeyCode 
    Case vbKeyTab, vbKeyReturn, vbKeyDown, vbKeyUp 
        Application.ScreenUpdating = False 
        bBackwards = CBool(Shift And 1) Or (KeyCode = vbKeyUp) 
        bForwards = (KeyCode = vbKeyTab) Or (KeyCode = vbKeyDown) 
        If bBackwards Then 
            TextBox1.Activate 
             'Since this is the last control, stay on the button
        ElseIf bForwards Then 
            CommandButton1.Activate 
        Else 
             'Only remaining possibility is Return key
            Call CommandButton1_Click 
        End If 
        Application.ScreenUpdating = True 
    End Select 
End Sub 
Private Sub CommandButton1_Click() 
    Sheet2.Cells(1, 1).Value = "test" 
    Sheet2.Activate 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Post your answer or comment

comments powered by Disqus
In Excel 2003 I can't use the Enter key to to to a hyperlink destination. It
only works with a mouse click.

Also how do I stop the Web toolbar appearing everytime I go to the hyperlink
destination.

These are both very annoying changes in Excel 2003.

Hello,

Is there a way in Excel to use the enter key to offset to the next column
instead of the next row after data is entered (without using vba)?

Thanks!

Melissa

I'm working with an invoice document that someone created on Excel 2003.
It's a great document, but we want to program either the TAB key or the ENTER
key to move to a different cell within the worksheet. Example: After typing
the Date, enter automatically to Invoice Number, then enter automatically to
Company Name, and so on. Is there a way to start at cell N1 ,then program
the Enter key to automatically go to cell N7 after that, then C9 after that
and so on.

Any information is GREATLY appreciated!

Hi All,

I have a Form with 2 passwords on it. After entering the first Password, an OK button is clicked and if the Password is correct, then the Form shifts focus to the next Password Textbox and disables the first. I would like to be able to use EITHER the Mouse Click or the ENTER key on the Keyboard to shift the focus if the first password is correct, but I am having no success. Can anyone help me here please?

Regards,

Bill

Hi,

I am not versed in Access, more of Excel person, but I have to extract reports from a database for an excel tracking sheet. I have modified the it so instead of having to use the mouse to click "Run Report", I am trying to run it when I hit the Return key.

There is a text box that I enter a report number in, then click "run report".. I tried as many of the "on" this or that's listed under the Event tab.. but like On Exit, if I click out of it, it will run the report (not desired)....

basically is there a way to enter a number and hit the Enter key to run the report, but beable to type something, change my mind and click another control with out it running the report, which is what it is doing now.

Sorry for my lack of Access Knowledge...

Thanks in Advance.

Joe

Hi izzit possible to 'bind' the [Enter] key to command button1 on this sheet?(works on this sheet only).
Then when the user press [enter] key, it'll click on command button1, and run all the codes on command button1?

On a userform I have a textbox (TextBox1) and a command button (CommandButton1).

If a user leaves TextBox1 by pressing the Enter key I want the userform to react as though the user had clicked CommandButton1. This should not happen if they leave TextBox1 any other way (eg pressing Tab or clicking somewhere else on the form).

Can someone suggest what code to add to the TextBox1 code?

I've assumed it's along the lines of

Private Sub TextBox1_{some code here that recognises the Enter key being pressed}
Call CommandButton1_Click()
End Sub

but I can't find what to put in for 'some code here ...'

Thanks in advance.

I created a combo box from the control toolbox toolbar. It works fine and
the user can type in a few letters to narrow the list which displays in the
combobox. Once the list has been shortened they will click on the item they
want. Very handy.

But if the item is first in the list of available items, the click doesn't
do anything and the Click Event doesn't trigger. So my Macro -- Private Sub
ComboBox1_Click() -- doesn't run. If the clicked item is 2nd (or further
down) on the list the Click Event triggers fine and everything works.

Any ideas how to get the click event to work for the first item in the list?

Thanks!

Dale

I have a textbox and two commandbuttons on a userform. One of the buttons is
to unload the userform and the other is to add the contents from the textbox
to a worksheet. I do not want to click the button everytime. I would just
like to press the enter key to execute the code. I have looked every where
to see how this is done but with no success. Any help will be much
appreciated.

hey folks,

I'm creating a button on a worksheet using VBA and then want to add code to the buttons click event. So far i have this:

dim btnCode as string
dim ws as worksheet

set ws5 = thisworkbook.worksheets("Graphs")

ws5.OLEObjects.Add classtype:="Forms.commandbutton.1", _
                                  link:=False, _
                                  Left:=2783, _
                                  Top:=0, _
                                  Width:=90, _
                                  Height:=32

    btnCode = "Sub CommandButton1_Click()" & vbCrLf
    btnCode = btnCode & "Application.ScreenUpdating = False" & vbCrLf
    btnCode = btnCode & "Call diffTable" & vbCrLf
    btnCode = btnCode & "Application.ScreenUpdating = True" & vbCrLf
    btnCode = btnCode & "End Sub"
    Debug.Print btnCode
   
    ThisWorkbook.VBProject.VBComponents("Graphs").CodeModule.AddFromString (btnCode)

On the last line I receive a "Run Time error '9': Subscript outof range". Any able to shed some light on this?

Hi All,
I have spent all day searching for a solution to what I thought was a simple problem.
All controls are activex controls placed directly onto an excel worksheet.

I have two list box placed within a single page of a mulitpage control.
I want to capture a change event in one and update another based on the change. (Will iterate through each listItem).
Have already tried embedding a command button in the page and can not even get that to trigger a click event that I could use to invoke the update.

All I can get to work are the (page) change or the (page) click events. Both not much help.

The only workaround I can think of is by putting a command button outside of the mulitpage, and setting its visible state based on page selected. But not a very pretty solution.

Anybody got any ideas?

Thanks in advance
Martin

Greetings,

I've created a calendar user form, much like many of the examples I seen posted here. I've tried all that I could find but none would allow the user to use the Enter Key to make the selection like the mouse click.

I would like the use of the Enter Key to produce the same results as a Mouse Click.

Any suggestions - Thanks, Bryan

In a listbox of item numbers.

I can successfully mouse click any item number in the list and the matching item is selected on the main sheet.

If a keyboard letter key is selected the first of the corresponding item numbers that starts with that letter (if any) is selected and I can continue to scroll through those corresponding item numbers by continuing to press the same letter key.

But…. the enter key will not select the item the same as mouse clicking will.

Is it possible to program the enter key to act the same as a mouse click?

Also, using the keyboard up-down arrow keys will scroll through the items in the list and also select the matching item on the sheet concurrently.

Is it possible to disable the up-down arrows from having any control on my sheet?

Thank you

I am running the forowing code to enter data on a separate sheet. This works ok providing the enter key is used so that the activecell row is always the next line down.

However some users enter data and use the mouse to select another cell somewhere in the workbook. This has the effect that my activecell row is not the one I want !!!

Can this be stopped or can the macro be changed to know the row number of the cell that has just been amended?

Thank you in advance

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

 

Application.DisplayAlerts = False

Set KeyCells = Range("F2:F503")

    If Target.Cells.Count > 1 Or IsEmpty(Target) Then GoTo 1
    If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
    'MsgBox "Cell " & Target.Address & " has changed."
    WK = ActiveCell.Offset(-1, -5).Value
    Customer = ActiveCell.Offset(-1, -4).Value
    MyType = ActiveCell.Offset(-1, -3).Value
    Sheets("Sheet1").Select
    Range("A9").Value = WK
    Range("A11").Value = Customer
    Range("C17").Value = MyType
    
    MyPrinter = Application.ActivePrinter
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
        "exchange2003Production HP 4250", Collate:=True
    Application.ActivePrinter = MyPrinter
    Sheets("Inspection").Select
    Application.Run "Inspection.xls!Mailer"

1   End If
End Sub


Hello

I have a useform with a listbox on it. I want to populate the listbox with file names which the user has selected. Initially the listbox is empty, except for a dummy If the user selects this, I call a routine which prompts him to select a file and this is added to the listbox. So now the listbox contains a item as well as the selected file. The user can then carry on adding files or selecting from the list of already added files.

Here's my problem....when setting the listindex property of the listbox, this seems to generate a "click" event. (I can track the listbox click event handler ebing called.) And setting the listindex property within the code called by the click event results in the click event being called recursively! I've tried disabling events but this doesn't seem to work. Any suggestions please - I'm going frantic!

Regards
Hedges

Here is a snippet of the code below.

Code:
Private Sub UserForm_Initialize()
Dim i As Integer
   
   Application.EnableEvents = False
   Set InpCln = New Collection
   ClnInFCln.Add InpCln, ListBox1.Name
   ClnInFCln(ListBox1.Name).Add "", key:=""
   ListBox1.AddItem "", 0
   ListBox1.ListIndex = 0 'Generates a click event!?

   Application.EnableEvents = True
End Sub

'If "" selected, calls routine to select file.
'Adds file name to collection of names and then updates ListBox list with the contents of this collection

Sub ListBoxManager(ctrl As Object)
Dim i As Integer
Dim newfirst As String 'Name new first place filename
Dim newfile As String
   
   Application.EnableEvents = False
   counter = counter + 1
   If ctrl.Value = "" Then
      newfile = FullFileName("C:JohnTest")
      If newfile  "" Then
         ClnInFCln(ctrl.Name).Add newfile, key:=newfile, after:=1
      End If
   Else
      newfirst = ctrl.Value
      ClnInFCln(ctrl.Name).Remove newfirst
      ClnInFCln(ctrl.Name).Add newfirst, key:=newfirst, after:=1
   End If
   
   'Now update contents of ListBox
   ctrl.Clear
   For i = 1 To ClnInFCln(ctrl.Name).count
      ctrl.AddItem ClnInFCln(ctrl.Name).Item(i)
   Next
   ctrl.ListIndex = 0         'Calls ListBox Manager recursively!
   Application.EnableEvents = True
End Sub

Private Sub ListBox1_click()
   Call ListBoxManager(ListBox1)
End Sub


In Excel, how do I sent a page from a workbook via e-mail to several addresses?
In Excel, how do I set the Enter key to move to the right, not down.

Thanks,
Art

I have posted this as a new user but hought I may get a better reponse
here.I am using ms excel to enter data, 1 for Yes and 2 for No, for 300
items/questions listed successively in column A. The data is then
tallied on another excel page. However, I would like to have the entry
of 1 or 2 provide the jump to the next cell down ( each time1 or 2 is
entered) rather than have to always press enter key to go to the next
cell. Any ideas how to proceed?

--
johnexcel
------------------------------------------------------------------------
johnexcel's Profile: http://www.excelforum.com/member.php...o&userid=26600
View this thread: http://www.excelforum.com/showthread...hreadid=398818

Dear group

How is it possible to assign a click event to a checkbox that has been placed within a worksheet using the following code..

Dim OleObjectsObject As OLEObject
Dim OleControl As OLEObjec
Dim CheckBox As MSForms.CheckBo

Set OleControl = OleObjectsObject.Add(ClassType:="Forms.CheckBox.1"
Set CheckBox = ExcelWorkSheet.OLEObjects(OleControl.Name).Objec

Best regards

Jim...

Hi,
I have an excel form where i add values to a combo box.

i =1
Controls("ComboBox" & i).RowSource = Sheets("IO").Range("I" & i).Value
Controls("ComboBox" & i).Value = Sheets("IO").Range("L" & i).Value

After the third line of code is executed, combo box click event is called. I want to supress the click event call. I was trying to use the SendMessageBystring method but it is giving an error. Can someone suggest any solutions for this?

Thanks,
Naren.

I have posted this as a new user but hought I may get a better reponse here.I am using ms excel to enter data, 1 for Yes and 2 for No, for 300 items/questions listed successively in column A. The data is then tallied on another excel page. However, I would like to have the entry of 1 or 2 provide the jump to the next cell down ( each time1 or 2 is entered) rather than have to always press enter key to go to the next cell. Any ideas how to proceed?

Is there code for worksheet activate event that will reprogram the Enter Key to mimic the Tab Key such that if you hit Enter, the cell to the right of the active cell is selected? I've searched the net for several hours today and find nothing that addresses this question.

Also, the Enter Key would have to be normalized on worksheet deactivate.

Thanks for your help. J

Following on from an earlier thread which asked how to select an optbutton
and fire its click event from the keyboard, a new issue arises. No criticism
is intended on the suggested code and I hope the contributor is able to see
this new thread.

In contrast to the more conventional method of keying in the button's
accelerator then spacebar to fire the click event, I used the suggested code
which avoids the spacebar as reducing keyboard effort is a desirable aim.

For brevity 1 button is shown as the last 2 subs are repeated with relevant
name changes for each new button required.

Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As
Long) As Integer

Private Sub optYes_Enter()
If GetAsyncKeyState(vbKeyMenu) And &H8000 Then optYesValue =
True
End Sub

Private Sub optYes_KeyDown(ByVal KeyCode As
MSForms.ReturnInteger, ByVal Shift As Integer)
If KeyCode = Asc(UCase(lblYes.Accelerator)) And Shift = 4 Then
optYes.Value = True
End Sub

There are 2 scenarios:
1. 2 optbuttons, Yes and No, with appropriate labels. Function is to
provide choice to amend either a txtbox or combobox data. On selection,
focus is switched to the relevant control and the optbuttons hidden. An
accelerator key is attached to the label (not optbutton caption) and the tab
order is correctly sequenced to suit.
2. 1 optbutton which hides the frame in which it is located.

All 3 click events fire correctly using the mouse. Disarmingly, all 3 work
when using the conventional method of accelerator then spacebar Using
the code above 1 event works but 2 fail at the point when trying to hide the
buttons.

Click events:
Private Sub optYes_Change() 'Works as expected
cboVehReg.Enabled = True
lblYes.Visible = False
lblNo.Visible = False
optYes.Visible = False
optNo.Visible = False
txt2.SetFocus
End Sub

Private Sub optNo_Change()
cboVehReg.Enabled = True
lblYes.Visible = False
lblNo.Visible = False
optYes.Visible = False
optNo.Visible = False 'Fails here
cboVehReg.Value = ""
cboVehReg.SetFocus
End Sub

The 3rd event in scenario 2 is similar.

I could understand if the cause of failure was in trying to hide the buttons
'recursively' ie before the control has lost focus but that works succesfully
in the other 2 handling methods. It also works with 1 button using this
method. Paradoxically it is the button that works which becomes the anomaly.

Does anyone knows what's happening here? I would appreciate your views.

T.I.A.

Geoff

I recently switched to Excel 2003. I create a large number of hyperlinks in Excel and activate the links quite often. In the old version of Excel I had, I could highlight the cell and press the enter key to activate the link, but in 2003 can only activate the links using the mouse. The continual clicking has quickly grown tiresome.

Is there a setting that can be adjusted in Excel 2003 that allows for activation of hyperlinks with the enter (or return) key?

Hello

I am trying to write to a file but cannot use the enter key to start a new line. I have tried char(10) and it puts a
square box. I tried every chr in a loop and the output file has no characters on the second line, therfore no enter
key. I have also tried vbkeyreturn and does nothing.

Anyone know how to work around this little problem?

Heres my code ( its been simplyfied)

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f, ts
Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile "c:cd.asc" 'Create a file
Set f = fs.GetFile("c:cd.asc")
Set ts = f.OpenAsTextStream(ForWriting, TristateFalse)
ts.write "hello"
ts.write Chr(10)
ts.write "hello"
ts.Close

Thanks in advance

Mike


No luck finding an answer? You could always try Google.