Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-27-2014, 11:00 PM
htieK htieK is offline Code to send cursor to predefined cell Windows 7 32bit Code to send cursor to predefined cell Office 2010 32bit
Novice
Code to send cursor to predefined cell
 
Join Date: Oct 2014
Posts: 9
htieK is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 10-28-2014, 07:34 AM
BobBridges's Avatar
BobBridges BobBridges is offline Code to send cursor to predefined cell Windows 7 64bit Code to send cursor to predefined cell Office 2010 32bit
Expert
 
Join Date: May 2013
Location: USA
Posts: 700
BobBridges has a spectacular aura aboutBobBridges has a spectacular aura about
Default

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
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.
Reply With Quote
  #3  
Old 10-30-2014, 02:07 AM
htieK htieK is offline Code to send cursor to predefined cell Windows 7 32bit Code to send cursor to predefined cell Office 2010 32bit
Novice
Code to send cursor to predefined cell
 
Join Date: Oct 2014
Posts: 9
htieK is on a distinguished road
Default

Sorry Bob, have been away. Will look at this tomorrow! - Keith
Reply With Quote
  #4  
Old 11-01-2014, 12:30 AM
htieK htieK is offline Code to send cursor to predefined cell Windows 7 32bit Code to send cursor to predefined cell Office 2010 32bit
Novice
Code to send cursor to predefined cell
 
Join Date: Oct 2014
Posts: 9
htieK is on a distinguished road
Default

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
Reply With Quote
  #5  
Old 11-01-2014, 12:36 AM
htieK htieK is offline Code to send cursor to predefined cell Windows 7 32bit Code to send cursor to predefined cell Office 2010 32bit
Novice
Code to send cursor to predefined cell
 
Join Date: Oct 2014
Posts: 9
htieK is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 11-05-2014, 08:38 PM
htieK htieK is offline Code to send cursor to predefined cell Windows 7 32bit Code to send cursor to predefined cell Office 2010 32bit
Novice
Code to send cursor to predefined cell
 
Join Date: Oct 2014
Posts: 9
htieK is on a distinguished road
Default

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.
Reply With Quote
  #7  
Old 11-06-2014, 09:57 AM
whatsup whatsup is offline Code to send cursor to predefined cell Windows 7 64bit Code to send cursor to predefined cell Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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
Reply With Quote
  #8  
Old 11-06-2014, 10:30 PM
htieK htieK is offline Code to send cursor to predefined cell Windows 7 32bit Code to send cursor to predefined cell Office 2010 32bit
Novice
Code to send cursor to predefined cell
 
Join Date: Oct 2014
Posts: 9
htieK is on a distinguished road
Default Thanks!

Thanks Whatsup, I'll have a play with that tomorrow.
Reply With Quote
  #9  
Old 11-11-2014, 11:16 PM
htieK htieK is offline Code to send cursor to predefined cell Windows 7 32bit Code to send cursor to predefined cell Office 2010 32bit
Novice
Code to send cursor to predefined cell
 
Join Date: Oct 2014
Posts: 9
htieK is on a distinguished road
Default 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.
Reply With Quote
  #10  
Old 11-12-2014, 03:42 AM
whatsup whatsup is offline Code to send cursor to predefined cell Windows 7 64bit Code to send cursor to predefined cell Office 2010 32bit
Competent Performer
 
Join Date: May 2014
Posts: 137
whatsup will become famous soon enough
Default

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.
Reply With Quote
Reply

Tags
send cursor, vba code

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code to send cursor to predefined cell 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 to send cursor to predefined cell Code for Changing Cell Backgrounds leroytrolley Excel 2 12-05-2008 02:05 AM
Code to send cursor to predefined cell code to save / rename / send attachments unit213 Outlook 1 09-26-2007 08:15 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:24 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft