Hi all, I have a spreadsheet where I have to fill in the following cells one
after the other if data is entered into cell C1: they a D1, E1 then C3, C4
and C5. These are the only cells needed to be input in that order, can anyone
help me with this.
Thanks all in anticipation of your appreciated help.


--
delmac

Hi,

Maybe you want something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldTarget As Range
If OldTarget Is Nothing Then
Set OldTarget = Target
Else
Select Case OldTarget.Address
Case "$C$1"
Range("D1").Select
Case "$D$1"
Range("E1").Select
Case "$E$1"
Range("C3").Select
Case "$C$3"
Range("C4").Select
Case "$C$4"
Range("C5").Select
End Select
Set OldTarget = ActiveCell
End If
End Sub


Copy this into the code module for the sheet you are interested in.
Then, pressing enter when in cell C1 will get you to D1, pressing enter
in cell E1 will get you to C3 etc. If this isn't what you are looking
for then you need to be a little more clear in your question.

HTH

-John Coleman
delmac wrote:
Hi all, I have a spreadsheet where I have to fill in the following cells one
after the other if data is entered into cell C1: they a D1, E1 then C3, C4
and C5. These are the only cells needed to be input in that order, can anyone
help me with this.
Thanks all in anticipation of your appreciated help.


--
delmac

Random observation: my last 2 cases seem superfluous since passing from
c3 to c4 is the default behavior. A few points about my code which
might lead to some undesirable behavior:

1) It *forces* you to go through the sequence. So you can't, for
example, just change E1 and then go somewhere else - you have to go to
C3 next, etc. If you want this behavior - then forget about what I said
about the last 2 cases being superfluous. If you don't want this
behavior, then maybe some similar code with the worksheet change event
could work (though this would have troubles of its own - you don't
sequence unless you *change* the cells - just pressing enter to keep
the current value would no longer work).

2) You can pick up the sequence in mid-stride. Starting at E1 (without
previously being in C1) would take you to C3 next, etc. If this is a
problem for any reason, it would be easy enough to introduce a boolean
flag (something called, say, "started") which is set to true the first
time C1 is entered and is set back to false when C5 is left.

3) If you save the workbook while C1 is selected then the code doesn't
work next time it is opened. A manual work around is to first select
say A1 before moving back to C1. A more principled approach would be to
initialize OldTarget in the workbook open event - but I don't see much
point in going this rout for what would probably be a very infrequent
hassle in a macro that sounds like it's just designed to save a few
mouse moves.

HTH

-John Coleman

John Coleman wrote:
Hi,

Maybe you want something like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldTarget As Range
If OldTarget Is Nothing Then
Set OldTarget = Target
Else
Select Case OldTarget.Address
Case "$C$1"
Range("D1").Select
Case "$D$1"
Range("E1").Select
Case "$E$1"
Range("C3").Select
Case "$C$3"
Range("C4").Select
Case "$C$4"
Range("C5").Select
End Select
Set OldTarget = ActiveCell
End If
End Sub


Copy this into the code module for the sheet you are interested in.
Then, pressing enter when in cell C1 will get you to D1, pressing enter
in cell E1 will get you to C3 etc. If this isn't what you are looking
for then you need to be a little more clear in your question.

HTH

-John Coleman
delmac wrote:
Hi all, I have a spreadsheet where I have to fill in the following cells one
after the other if data is entered into cell C1: they a D1, E1 then C3, C4
and C5. These are the only cells needed to be input in that order, can anyone
help me with this.
Thanks all in anticipation of your appreciated help.


--
delmac