[Solved] VBA : Keyboard enter button

  • Hi there


    Could someone help please?


    I have written a small VBA excel program that does some calculations and then prints the results.


    I want to program the enter key on the keyboard as well as the numeric keypad to perform the calculate sub routine when they are pressed but am at a total loss as to how to do this.


    Also, if someone has given me a solution do I send them a personal e-mail to thank them or do I post the thanks on the board?


    Thanks
    Ursula

  • Not sure I understand completely but check out 'OnKey' in the VBE help:


    ENTER (numeric keypad) {ENTER}
    ENTER ~ (tilde)


    using Application.OnKey etc.


    HTH

    <a href="http://www.mrexcel.com/relayforlife.shtml" target="new"><img src="http://www.myimgs.com/data/vonpookie/anne_relay.gif"></a>

  • Hi there Iridium


    I have tried the Application.OnKey solution but I must have the code wrong because it is not working.


    I have created a userform into which the user enters their infomation and I have added a calculate button which does the pasting and calculating but I can't get the enter keys to work. When I press them the cursur jumps to the different fields according to the tab order.


    Ursula

  • Hi Ursla I was just about to ask the same question. I know that you can go to the visual basic editor, select the sheet or workbook you want to do these calculations in. Where it say General at the top change this to worksheet. Then over to the right change this from declarations to change. This will run your instructions whenever a cells value changes. Just make sure you give it a specific range to to work in ie use an if statement If target.column = 5...
    This way it will only run when cell values in column 5 are changed. Otherwise it will be applied to the entire sheet.



    My problem / question with this method is that it gives me a run error when I use the tab key after entering data. It works fine when I use the arrow or enter key or click on a new cell. How can I fix this. I would like it to only run when the enter key is pressed.

  • Hi wdyjdiy23, Thanks for your comments, I will try them and see how that works.


    Are you using a userform or just a spreadsheet? I just found out that if you use a userform the textboxes you use to enter data have behaviour properties for tab and enter keys so I am going to try and use those as well to solve my problem.


    Good luck.

  • Hi Ursula,
    When you have your userform displayed in the VBE, click on the Calculate button and then look at its properties. One of the properties is Default. Set that to TRUE and the button will then fire when the enter key is hit.


    When a question is satisfactorily answered, you should mark it Solved by pushing the Solved button. Adding a Thanks to the thread is not necessary, but is appreciated.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!