Execute VBA Routine on Enter Key

[rkjudy]

Simple question. How do I have a workbook execute VBA code when I hit 'Enter' anywhere on a specific sheet? I don't need the code to execute when I hit 'Enter' on any of the other sheets in the workbook, just a specific sheet.

[Leith Ross]

Hello rkjudy,

It is possible to do. Because the Enter key is so vital to all Excel operations, it begs the question - Why? It would not be very efficient or practical from a coding standpoint. There may be a better solution but you have have to provide more details about what you're doing.

[rkjudy]

Thanks Leith. Here's what I'm trying to do. The workbook opens to a sheet with the focus on cell C14. The user is expected to put in a number (dollar amount) in this cell. After he enters the number and hits 'enter', I want to execute a routine that will perform a calculation based on THAT number and a number on another sheet and place the result in cell E21 on the original sheet.

Basically, I can't get the answer I need until a number is placed in cell C14.

Any alternatives would be appreciated.

[Leith Ross]

Hello rkjudy,

The value can be extracted from the cell after it is updated using the Worksheet_Change event. Each sheet has one of the event modules. Here is an example. This multiples C14 by the value in cell G5 on Sheet2 and stores the result in E21 of the active sheet.

Please Login or Register  to view this content.

[rkjudy]

Below is the code I entered, but nothing happens when I enter a number in cell C14.

Please Login or Register  to view this content.

BTW, the sheet name where this occurs is 'PriceWorkup'.

What do you think I've done wrong? Also, will you tell me exactly where this code is supposed to go. I think I have it in the right place, but just to be sure.

[shg]

The code is in a worksheet module (as it should be), not a code module or the ThisWorkbook module?

Set a breakpoint on the first executable line and see if the code is being triggered.

[rkjudy]

Nothing seems to work. FYI, I'm running Excel 2003. Do I need to turn something on the Object Library?

[Brane Ded]

A side suggestion but you may not want to assign the macro to the enter key as Leith stated it is a vital keyboard function to Excel. Say you wanted to change a value or modify your formulas, everytime to hit the enter key it will run your macro instead of completing your initial task.

You may be better assigning your macro to an object you can place on your spreadsheet next to the cell C14. For example insert a shape, say a rectangle, right next to cell C14 and put some text in it like "Go" or "Start" then right-click the shape and hit "Assign macro" and assign the macro you want to run to that shape. This way your enter key stays free and after entering the number in C14 you only have to mouse over to the rectangle and click on it to run your macro.

[rkjudy]

That would be OK also. I was just trying to automate the macro execution without the extra step of hitting 'Go'. Thanks.

[Leith Ross]

Hello rkjudy,

If you post your workbook, I'll take a look at why it isn't working right.

[rkjudy]

Let's try this. Let's forget the 'execute on enter' issue. How about execute a macro on change. I have a workbook that opens to a sheet with focus on cell C14 which is empty (null). I would like to have a macro execute if cell C14 changes in any way (ie. user enters a dollar amount). I would also like for the macro to re-execute if the value changes again (ie. the user enters a DIFFERENT different dollar amount). Any ideas on this?

[shg]

would like to have a macro execute if cell C14 changes in any way

That's what your code is set up to do now, provided that C14 is changed by manual entry (i.e., not the result of a formula it contains).

The code MUST be in the appropriate sheet module, not a code module.

Macros MUST be enabled. Set macro security to Medium and enable them when prompted.