Hi folks,

I have been asked to resubmit my question here:

Is there a way to create a "property" (or a self-executing macro) for a cell that always sends the cursor to a specific cell after entering data in this one?

It would need to be able to recognise immediately a value (any value) is entered into a particular cell and send the cursor to a pre-defined cell.



I can't imagine why this isn't one of the most common questions!

Cheers,
Keith

It's me again, Keith. Do you want this to execute in every worksheet of workbook, or only in a particular worksheet? I ask because for both solutions you'll write approximately the same program, but if it's to operate on only a particular worksheet then you'll store it in the "Excel Object" that corresponds to that worksheet, but if in every worksheet then you'll put it in the ThisWorkbook object. You can see those in your VBA editor under "Microsoft Excel Objects" rather than "Modules".

(And by the way I'm assuming that you already know something about the VBA editor. If you don't—if this'll be your first macro—I should back up and start at the beginning.)

The macro you write will be named "Worksheet_Change"—it must have that exact name, because that way you cause it to respond to an event with that exact name—and it has to have the prescribed list of arguments with exactly the right data types, though each argument can be named what you choose. All this is defined here; take a look. Within those limitations your macro can do whatever you like.

I have a short Worksheet_Change macro that looks like this:This goes in a timesheet I maintain. Whenever I enter a value in one of the columns headed "From", it put's today's date in the date column. If it already had today's date in that cell, fine; but if it had some other date (in case I made a mistake) it displays a warning message. That's all it does. Maybe you can use it as a sort of template.

Sorry Bob, have been away. Will look at this tomorrow! - Keith

Hi Bob,
Yes, it is to happen only on 1 worksheet, although there are several cells from which I want to send the cursor, to a different relevant cell.
My experience so far is in recording macros, identifying commands in them and copy/pasting as necessary. I have never learned VB.
I gather from what you are saying that "Worksheet_Change" is a reserved macro name within Excel, or within VBA. I also gather, or I guess "hope" that it will enable entry of a different set of instructions for different cells, each to operate alone if a value is entered in the relevant cell.
I don't know how to store "in an Excel Object corresponding to the worksheet", so perhaps you could shine a light here?
Back Monday
Many thanks,
Keith

PS, Unfortunately I had to log in again to post last message and inadvertently posted an older version from the clipboard. I meant to tell you I'd checked out the link and I think I can assemble a working macro from that, other than the "Excel Object" bit. It does seem to imply that I can have different sets of instructions for different cells. Will look again on Monday.

Excel is so much more complicated to get it to do things automatically than "Enable", a program I used from about 1985 until Y2k! It handled relative cell references in macros better too. I have found that the relative cell tool in 2010 (in macros) has lost capabilities of Office 2000, and have to copy macros in from files created under 2000 to get them to work.
Cheers

Hi again Bob,

I have had a good look at both yours and the samples on the link. I am assuming that by "store it in the "Excel Object" that corresponds to that worksheet", I should open the VBA window, select the relevant project, which is "VBAProject(Accounting.xlsb)", then "Microsoft Excel Objects" and under that, "Sheet 7 (Cashup)" which is the sheet, or tab that I want the macro to have effect in, and then type the macro into the window at right.

To this point, while I have recorded, copied and edited many macros, I've never type one from scratch. I think its really time I applied myself to learning VBA, becaue I'm having a lot of difficulty in recognising the commands and working out what is needed in my case. In the meantime, if my above assumption is correct, if you could give me the code that recognises when data is entered, say into cell K12 and moves the cursor to G11 ready to accept data, that will get me on the way.

Many thanks, Bob.

Hi @all

Use Intersect() to verify if the changed cell in deed is the one which shall trigger a certain Selection. The rest is easy:
Changing the value in K12 and pressing "Enter" will select G11.

Cheers

Thanks Whatsup, I'll have a play with that tomorrow.

Thanks Whatsup, your code worked. It did take me a while to get to it but yes, its almost doing what I want. The only issue is if I leave the cell without entering a value, the code doesn't do anything. I'm realising I was a little too literal in my original question. I'd like the code to respond "leaving the cell" rather than "entering a value into the cell".

Are you able to help with that one?

Many thanks for your help.

Htiek, then you will need a construct like this with a public variable:
The code sets blnJump to True once you enter K12. You can either change K12 or leave it unchanged. Leaving K12 will trigger the event again, and will select G11 setting blnJump to False.
The disadvantage: SelectionChange is triggered all the time with selecting a cell within the sheet (though it won't happen anything unless you select K12). Personally I stay away from such events, but I can't think of another possibility.