Command button macro query

B

BeSmart

Hi al
Is there a function (eg Shift Cntrl R) or maybe I need to create a command button that when clicked, will return the user to the last cell they entered

Currently the user enters data on a row, then scrolls down heaps of rows (in which other data is entered) to the totals row (or they use the name box to select a defined cell that refers to the totals they want) and they check the results. They then want to return to the cell they just entered but are finding it cumbersome to find it

Any help would be greatly appreciated.
 
B

Bob Flanagan

It is not elegant, but the user can click on Edit, Undo, and then Edit, Redo
and that will put them back to the last modified cell and the cell will be
as modified.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel

BeSmart said:
Hi all
Is there a function (eg Shift Cntrl R) or maybe I need to create a command
Click to expand...
button that when clicked, will return the user to the last cell they
entered?
Currently the user enters data on a row, then scrolls down heaps of rows
Click to expand...
(in which other data is entered) to the totals row (or they use the name box
to select a defined cell that refers to the totals they want) and they check
the results. They then want to return to the cell they just entered but are
finding it cumbersome to find it.
 
A

Anders S

BeSmart,

Here's one possible way to get started.

*** Enter the following code in the worksheet code window (right-click the worksheet tab and choose View Code)

'-----
Option Explicit

Public LastEnteredCell As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Set LastEnteredCell = Target
End Sub

Sub goBack()
LastEnteredCell.Select
End Sub
'-----

*** To assign the shortcut - Ctrl+Shift+R - do Tools>Macro>Macros, select goBack, click Options and enter R in the shortcut box.

HTH
Anders Silven
 
K

kkknie

If I read the original post correctly, the user will be entering mor
than one piece of data in the lower rows. Therefore the simpl
worksheet_change suggestion probably won't do the trick. (You did sa
it was only to get them started...)

But, building on it a bit, you could use an array and save the last 1
positions (or more if you wanted). My solution doesn't have a "g
forward" option, but it could be implemented using an array inde
pointer. Give this a shot:


Code
-------------------
Dim adrLast(10) As String

Private Sub Worksheet_Change(ByVal Target As Range)

Dim i As Integer

For i = 1 To 10
adrLast(i) = adrLast(i - 1)
Next
adrLast(0) = Target.Address

End Sub

Sub GoBack()

Dim i As Integer

Range(adrLast(0)).Select
For i = 1 To 10
adrLast(i - 1) = adrLast(i)
Next

End Su
 
B

BeSmart

Thanks guys - I tried Ander S's solution and it works brilliantly
Muchly appreciation
 
Top