Code to send cursor to predefined cell

htieK · #1 11-05-2014, 08:38 PM

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.

whatsup · #2 11-06-2014, 09:57 AM

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

Changing the value in K12 and pressing "Enter" will select G11.

Cheers

htieK · #3 11-06-2014, 10:30 PM

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

htieK · #4 11-11-2014, 11:16 PM

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.

whatsup · #5 11-12-2014, 03:42 AM

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 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.

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