#1
|
|||
|
|||
Code to send cursor to predefined cell
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 |
#2
|
||||
|
||||
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: Code:
Private Sub Worksheet_Change(ByVal Tgt As Excel.Range) If Cells(1, Tgt.Column).Value <> "From" Then Exit Sub Set co = Cells(Tgt.Row, 1) ' If there's already a date there, it'd better be today's. vd = co.Value If Not IsEmpty(vd) Then If vd <> Date Then Abend "Not today's date." End If ' Otherwise make it so. co.Value = Date End Sub |
#3
|
|||
|
|||
Sorry Bob, have been away. Will look at this tomorrow! - Keith
|
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 Last edited by htieK; 11-01-2014 at 12:37 AM. Reason: minor spell correct, and minor addition. |
#6
|
|||
|
|||
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. |
#7
|
|||
|
|||
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: Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("K12")) Is Nothing Then Range("G11").Select End If End Sub Cheers |
#8
|
|||
|
|||
Thanks!
Thanks Whatsup, I'll have a play with that tomorrow.
|
#9
|
|||
|
|||
Code worked - thanks.
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. |
#10
|
|||
|
|||
Htiek, then you will need a construct like this with a public variable:
Code:
Option Explicit Private blnJump As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If blnJump Then Range("G11").Select blnJump = False End If If Not Intersect(Target, Range("K12")) Is Nothing Then blnJump = True End If End Sub 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. |
Tags |
send cursor, vba code |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA code to select one word from a text in a cell and place that word in next cell | Shinaj | Excel | 2 | 05-01-2014 01:50 PM |
Macro code should find inv no in folder and send attachhed mail. | visha_1984 | Outlook | 0 | 01-30-2013 05:08 AM |
Displaying cursor position within cell | MiamiTom | Excel | 0 | 11-18-2011 09:02 AM |
Code for Changing Cell Backgrounds | leroytrolley | Excel | 2 | 12-05-2008 02:05 AM |
code to save / rename / send attachments | unit213 | Outlook | 1 | 09-26-2007 08:15 PM |