Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving between cells
Hi, I have constructed a sheet that the user has to input data into and then upon completion press an on sheet command button to confirm. My problem is simple (I hope), I want to be able to dictate which cell the curser will move to upo completion of each entry. My example is; Cell B2 - Input then Cell C7 - Input then Cell B6 - Input then Command Button Is there an easy way of doing this without forcing the user to use the mouse to select the cells they need in the right order? -- Alec H ------------------------------------------------------------------------ Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042 View this thread: http://www.excelforum.com/showthread...hreadid=522094 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving between cells
Look at this example which works with the DOWN-ARROW, which I find more simple than pushing a botton. It starts out at G11 and after filling out G11 you push the DOWN-ARROW. This makes the cursor jump to A14, etc. Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'macro jump cell If Target.Row = 12 And Target.Column = 7 Then 'jump A14 Target.Offset(2, -6).Select End If If Target.Row = 15 And Target.Column = 1 Then 'jump B14 Target.Offset(-1, 1).Select End If End Sub -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=522094 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving between cells
You should disable events in this procedure, and turn them back
on at the end. Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'macro jump cell Application.EnableEvents = False '<<<<<<<<<<< If Target.Row = 12 And Target.Column = 7 Then 'jump A14 Target.Offset(2, -6).Select End If If Target.Row = 15 And Target.Column = 1 Then 'jump B14 Target.Offset(-1, 1).Select End If Application.EnableEvents = True '<<<<<<<<<<< End Sub Otherwise, you'll find that the macro is calling itself. The SelectionChange code changes the Selection, which calls SelectionChange again. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "nsv" wrote in message ... Look at this example which works with the DOWN-ARROW, which I find more simple than pushing a botton. It starts out at G11 and after filling out G11 you push the DOWN-ARROW. This makes the cursor jump to A14, etc. Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'macro jump cell If Target.Row = 12 And Target.Column = 7 Then 'jump A14 Target.Offset(2, -6).Select End If If Target.Row = 15 And Target.Column = 1 Then 'jump B14 Target.Offset(-1, 1).Select End If End Sub -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=522094 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving between cells
Thank You :) -- Alec H ------------------------------------------------------------------------ Alec H's Profile: http://www.excelforum.com/member.php...o&userid=31042 View this thread: http://www.excelforum.com/showthread...hreadid=522094 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving between cells
...but Chip, until now I have run my spreadsheet without the disabling of events and it works perfectly. It is only a small one, actually just a form to be filled out in certain cells only and there are almost no calculations. I will of course put in the extra lines you recommend, but what can go wrong if I do not disable events? NSV -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=522094 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Moving between cells
I'm not Chip, but If you put a break point in your code (right near the top),
then stepped through the code after the break, you'd see what was really happening. Not quite what you expect. But with the speed of the pc (and the code you're running), you don't notice the difference. You can also see it by adding a single msgbox: Option Explicit Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'macro jump cell MsgBox "hi from worksheet change!" If Target.Row = 12 And Target.Column = 7 Then 'jump A14 Target.Offset(2, -6).Select End If If Target.Row = 15 And Target.Column = 1 Then 'jump B14 Target.Offset(-1, 1).Select End If End Sub And depending on what your code does, lots can go wrong or nothing can go, er, look wrong. Here's a worksheet_change event that looks like it would just add something to the cell below the changed cell: Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Target.Offset(1, 0) = "hi" End Sub But changing that cell below causes the event to fire, which causes the cell below to change which causes the event to fire......until excel gets tired (it does try to protect itself a little bit). I changed A1 and excel got tired when it changed A227 (xl2003). nsv wrote: ..but Chip, until now I have run my spreadsheet without the disabling of events and it works perfectly. It is only a small one, actually just a form to be filled out in certain cells only and there are almost no calculations. I will of course put in the extra lines you recommend, but what can go wrong if I do not disable events? NSV -- nsv ------------------------------------------------------------------------ nsv's Profile: http://www.excelforum.com/member.php...o&userid=26500 View this thread: http://www.excelforum.com/showthread...hreadid=522094 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
30 Day Moving Average Ignoring Blank Cells | Excel Worksheet Functions | |||
moving cells based certain value | New Users to Excel | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |