Is it possible to program so that when a users exits a cell he/she is forced
to another cell, ie when leaving d3 the cursor will got to d33

I know about cell protection etc but that will not do what I would like

Thanks

You can do this with a worksheet_change macro in the sheet code.
right click sheet tab>view code>left window select worksheet>right select
worksheet_change

more info needed but this is the basic idea

target.offset(30).select


--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Nigel" <(E-Mail Removed)> wrote in message
news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
> Is it possible to program so that when a users exits a cell he/she is
> forced
> to another cell, ie when leaving d3 the cursor will got to d33
>
> I know about cell protection etc but that will not do what I would like
>
> Thanks

Hi Nigel,

You could try the Selection Change event. Put this code behine the
worksheet object you're after:

Private m_bPostD3SelectCell As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If m_bPostD3SelectCell = True Then
Call SelectCell
End If
If Target.Address = "$D$3" Then
m_bPostD3SelectCell = True
End If
End Sub

Private Sub SelectCell()
Range("D33").Select
m_bPostD3SelectCell = False
End Sub

Hope that helps

Best regards

John


"Nigel" <(E-Mail Removed)> wrote in message
news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
> Is it possible to program so that when a users exits a cell he/she is
> forced
> to another cell, ie when leaving d3 the cursor will got to d33
>
> I know about cell protection etc but that will not do what I would like
>
> Thanks

not sure what more info is needed, user exits cell d3 I would like cursor to
go to d33

then they can select cell d4 make an entry and cursor will then go to c3

then they select d5 make an entry and on exiting it will go to gXX and so on

Cells are paired for example A10 is related to U10m B10 is related to W19
AND SO ON

"Don Guillett" wrote:

> You can do this with a worksheet_change macro in the sheet code.
> right click sheet tab>view code>left window select worksheet>right select
> worksheet_change
>
> more info needed but this is the basic idea
>
> target.offset(30).select
>
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Nigel" <(E-Mail Removed)> wrote in message
> news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
> > Is it possible to program so that when a users exits a cell he/she is
> > forced
> > to another cell, ie when leaving d3 the cursor will got to d33
> >
> > I know about cell protection etc but that will not do what I would like
> >
> > Thanks
>
>
>

>>AND SO ON

no need to SHOUT! I'm still confused since mind reading is not one of my
talents but, in the absence of a better explanation from you, in the
worksheet_CHANGE event.

=if target.address="$d$3" then range("c3").select
=if target.address="$d$5" then range("gxxx").select
and so on

I'm sure it could be simplified.

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Nigel" <(E-Mail Removed)> wrote in message
news:40E61E88-F59D-41BA-902C-(E-Mail Removed)...
> not sure what more info is needed, user exits cell d3 I would like cursor
> to
> go to d33
>
> then they can select cell d4 make an entry and cursor will then go to c3
>
> then they select d5 make an entry and on exiting it will go to gXX and so
> on
>
> Cells are paired for example A10 is related to U10m B10 is related to W19
> AND SO ON
>
> "Don Guillett" wrote:
>
>> You can do this with a worksheet_change macro in the sheet code.
>> right click sheet tab>view code>left window select worksheet>right select
>> worksheet_change
>>
>> more info needed but this is the basic idea
>>
>> target.offset(30).select
>>
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Nigel" <(E-Mail Removed)> wrote in message
>> news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
>> > Is it possible to program so that when a users exits a cell he/she is
>> > forced
>> > to another cell, ie when leaving d3 the cursor will got to d33
>> >
>> > I know about cell protection etc but that will not do what I would like
>> >
>> > Thanks
>>
>>
>>

Don,

I am so sorry I don't mean to shout, didn't look at the keyboard and see
caps lock on

Once again sorry


"Don Guillett" wrote:

> >>AND SO ON
>
> no need to SHOUT! I'm still confused since mind reading is not one of my
> talents but, in the absence of a better explanation from you, in the
> worksheet_CHANGE event.
>
> =if target.address="$d$3" then range("c3").select
> =if target.address="$d$5" then range("gxxx").select
> and so on
>
> I'm sure it could be simplified.
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Nigel" <(E-Mail Removed)> wrote in message
> news:40E61E88-F59D-41BA-902C-(E-Mail Removed)...
> > not sure what more info is needed, user exits cell d3 I would like cursor
> > to
> > go to d33
> >
> > then they can select cell d4 make an entry and cursor will then go to c3
> >
> > then they select d5 make an entry and on exiting it will go to gXX and so
> > on
> >
> > Cells are paired for example A10 is related to U10m B10 is related to W19
> > AND SO ON
> >
> > "Don Guillett" wrote:
> >
> >> You can do this with a worksheet_change macro in the sheet code.
> >> right click sheet tab>view code>left window select worksheet>right select
> >> worksheet_change
> >>
> >> more info needed but this is the basic idea
> >>
> >> target.offset(30).select
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Nigel" <(E-Mail Removed)> wrote in message
> >> news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
> >> > Is it possible to program so that when a users exits a cell he/she is
> >> > forced
> >> > to another cell, ie when leaving d3 the cursor will got to d33
> >> >
> >> > I know about cell protection etc but that will not do what I would like
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
>

Nigel

Maybe something similar to this?

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$C$2"
Range("C5").Select
Case "$C$5"
Range("E2").Select
Case "$E$2"
Range("E5").Select
End Select
End Sub

If there is a pattern, you might be able to use Offset(r,c) instead of
hard-coded references.


Gord Dibben MS Excel MVP


On Thu, 19 Oct 2006 12:26:02 -0700, Nigel <(E-Mail Removed)> wrote:

>
>Don,
>
>I am so sorry I don't mean to shout, didn't look at the keyboard and see
>caps lock on
>
>Once again sorry
>
>
>"Don Guillett" wrote:
>
>> >>AND SO ON
>>
>> no need to SHOUT! I'm still confused since mind reading is not one of my
>> talents but, in the absence of a better explanation from you, in the
>> worksheet_CHANGE event.
>>
>> =if target.address="$d$3" then range("c3").select
>> =if target.address="$d$5" then range("gxxx").select
>> and so on
>>
>> I'm sure it could be simplified.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Nigel" <(E-Mail Removed)> wrote in message
>> news:40E61E88-F59D-41BA-902C-(E-Mail Removed)...
>> > not sure what more info is needed, user exits cell d3 I would like cursor
>> > to
>> > go to d33
>> >
>> > then they can select cell d4 make an entry and cursor will then go to c3
>> >
>> > then they select d5 make an entry and on exiting it will go to gXX and so
>> > on
>> >
>> > Cells are paired for example A10 is related to U10m B10 is related to W19
>> > AND SO ON
>> >
>> > "Don Guillett" wrote:
>> >
>> >> You can do this with a worksheet_change macro in the sheet code.
>> >> right click sheet tab>view code>left window select worksheet>right select
>> >> worksheet_change
>> >>
>> >> more info needed but this is the basic idea
>> >>
>> >> target.offset(30).select
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "Nigel" <(E-Mail Removed)> wrote in message
>> >> news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
>> >> > Is it possible to program so that when a users exits a cell he/she is
>> >> > forced
>> >> > to another cell, ie when leaving d3 the cursor will got to d33
>> >> >
>> >> > I know about cell protection etc but that will not do what I would like
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>
>>
>>

Again so sorry I used your code and it works great, i added another sub and
here is that

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False
If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev
Set rngPrev = Target
idxPrev = rngPrev.Interior.ColorIndex
rngPrev.Interior.ColorIndex = 6

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'player 1
If Target.Address = "$A$10" Then Range("U10").Select
If Target.Address = "$C$10" Then Range("w19").Select
If Target.Address = "$E$10" Then Range("Y28").Select
If Target.Address = "$G$10" Then Range("AA37").Select
If Target.Address = "$I$10" Then Range("ac46").Select
end sub
the problem is this

I enter a value in A10 and hit tab the cursor moves to c10 and then onto u10
changes the color of u10 correctly but jumps back to c10. do you have any
idea why it may be doing this
"Don Guillett" wrote:

> >>AND SO ON
>
> no need to SHOUT! I'm still confused since mind reading is not one of my
> talents but, in the absence of a better explanation from you, in the
> worksheet_CHANGE event.
>
> =if target.address="$d$3" then range("c3").select
> =if target.address="$d$5" then range("gxxx").select
> and so on
>
> I'm sure it could be simplified.
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Nigel" <(E-Mail Removed)> wrote in message
> news:40E61E88-F59D-41BA-902C-(E-Mail Removed)...
> > not sure what more info is needed, user exits cell d3 I would like cursor
> > to
> > go to d33
> >
> > then they can select cell d4 make an entry and cursor will then go to c3
> >
> > then they select d5 make an entry and on exiting it will go to gXX and so
> > on
> >
> > Cells are paired for example A10 is related to U10m B10 is related to W19
> > AND SO ON
> >
> > "Don Guillett" wrote:
> >
> >> You can do this with a worksheet_change macro in the sheet code.
> >> right click sheet tab>view code>left window select worksheet>right select
> >> worksheet_change
> >>
> >> more info needed but this is the basic idea
> >>
> >> target.offset(30).select
> >>
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Nigel" <(E-Mail Removed)> wrote in message
> >> news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
> >> > Is it possible to program so that when a users exits a cell he/she is
> >> > forced
> >> > to another cell, ie when leaving d3 the cursor will got to d33
> >> >
> >> > I know about cell protection etc but that will not do what I would like
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>
>

Nigel,

I'm wasn't clear from your origianl post as to whether you want to move the
selected cell after entering data into a specific cell OR after a specific
cell was selected and not data/format change to place.

So at the moment the reason it jumps back to C10 after the color changes in
U10 is because that is a change event (ie not just data).

So on what basis do you want the changes to take place?

Best regards

John

PS Did you read my reply to your original post :
http://groups.google.co.uk/group/mic...567267c13f6263

"Nigel" <(E-Mail Removed)> wrote in message
news:33718ED9-0353-4B01-BFFD-(E-Mail Removed)...
> Again so sorry I used your code and it works great, i added another sub
> and
> here is that
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> Application.ScreenUpdating = False
> If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev
> Set rngPrev = Target
> idxPrev = rngPrev.Interior.ColorIndex
> rngPrev.Interior.ColorIndex = 6
>
> End Sub
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> 'player 1
> If Target.Address = "$A$10" Then Range("U10").Select
> If Target.Address = "$C$10" Then Range("w19").Select
> If Target.Address = "$E$10" Then Range("Y28").Select
> If Target.Address = "$G$10" Then Range("AA37").Select
> If Target.Address = "$I$10" Then Range("ac46").Select
> end sub
> the problem is this
>
> I enter a value in A10 and hit tab the cursor moves to c10 and then onto
> u10
> changes the color of u10 correctly but jumps back to c10. do you have any
> idea why it may be doing this
> "Don Guillett" wrote:
>
>> >>AND SO ON
>>
>> no need to SHOUT! I'm still confused since mind reading is not one of my
>> talents but, in the absence of a better explanation from you, in the
>> worksheet_CHANGE event.
>>
>> =if target.address="$d$3" then range("c3").select
>> =if target.address="$d$5" then range("gxxx").select
>> and so on
>>
>> I'm sure it could be simplified.
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Nigel" <(E-Mail Removed)> wrote in message
>> news:40E61E88-F59D-41BA-902C-(E-Mail Removed)...
>> > not sure what more info is needed, user exits cell d3 I would like
>> > cursor
>> > to
>> > go to d33
>> >
>> > then they can select cell d4 make an entry and cursor will then go to
>> > c3
>> >
>> > then they select d5 make an entry and on exiting it will go to gXX and
>> > so
>> > on
>> >
>> > Cells are paired for example A10 is related to U10m B10 is related to
>> > W19
>> > AND SO ON
>> >
>> > "Don Guillett" wrote:
>> >
>> >> You can do this with a worksheet_change macro in the sheet code.
>> >> right click sheet tab>view code>left window select worksheet>right
>> >> select
>> >> worksheet_change
>> >>
>> >> more info needed but this is the basic idea
>> >>
>> >> target.offset(30).select
>> >>
>> >>
>> >> --
>> >> Don Guillett
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "Nigel" <(E-Mail Removed)> wrote in message
>> >> news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
>> >> > Is it possible to program so that when a users exits a cell he/she
>> >> > is
>> >> > forced
>> >> > to another cell, ie when leaving d3 the cursor will got to d33
>> >> >
>> >> > I know about cell protection etc but that will not do what I would
>> >> > like
>> >> >
>> >> > Thanks
>> >>
>> >>
>> >>
>>
>>
>>

If data is changed in cell A10 I want it to go to cell U10 etc, stepping
thru the code line by line this is what happens

enter cell A10 enter a number
hit tab
cursor moves to c10 changes color of that cell
cursor jumps to u10 changes color of cell
cursor jumps back to c10 changes color of cell

if I remove the color change code then if I stpe thru the code this is what
happens
enter cell A10 enter a number
hit tab
cursor moves to c10
cursor jumps to u10

I am trying to get it to saty in cell U10 and have that change color

thanks

"John" wrote:

> Nigel,
>
> I'm wasn't clear from your origianl post as to whether you want to move the
> selected cell after entering data into a specific cell OR after a specific
> cell was selected and not data/format change to place.
>
> So at the moment the reason it jumps back to C10 after the color changes in
> U10 is because that is a change event (ie not just data).
>
> So on what basis do you want the changes to take place?
>
> Best regards
>
> John
>
> PS Did you read my reply to your original post :
> http://groups.google.co.uk/group/mic...567267c13f6263
>
> "Nigel" <(E-Mail Removed)> wrote in message
> news:33718ED9-0353-4B01-BFFD-(E-Mail Removed)...
> > Again so sorry I used your code and it works great, i added another sub
> > and
> > here is that
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >
> > Application.ScreenUpdating = False
> > If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev
> > Set rngPrev = Target
> > idxPrev = rngPrev.Interior.ColorIndex
> > rngPrev.Interior.ColorIndex = 6
> >
> > End Sub
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > 'player 1
> > If Target.Address = "$A$10" Then Range("U10").Select
> > If Target.Address = "$C$10" Then Range("w19").Select
> > If Target.Address = "$E$10" Then Range("Y28").Select
> > If Target.Address = "$G$10" Then Range("AA37").Select
> > If Target.Address = "$I$10" Then Range("ac46").Select
> > end sub
> > the problem is this
> >
> > I enter a value in A10 and hit tab the cursor moves to c10 and then onto
> > u10
> > changes the color of u10 correctly but jumps back to c10. do you have any
> > idea why it may be doing this
> > "Don Guillett" wrote:
> >
> >> >>AND SO ON
> >>
> >> no need to SHOUT! I'm still confused since mind reading is not one of my
> >> talents but, in the absence of a better explanation from you, in the
> >> worksheet_CHANGE event.
> >>
> >> =if target.address="$d$3" then range("c3").select
> >> =if target.address="$d$5" then range("gxxx").select
> >> and so on
> >>
> >> I'm sure it could be simplified.
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Nigel" <(E-Mail Removed)> wrote in message
> >> news:40E61E88-F59D-41BA-902C-(E-Mail Removed)...
> >> > not sure what more info is needed, user exits cell d3 I would like
> >> > cursor
> >> > to
> >> > go to d33
> >> >
> >> > then they can select cell d4 make an entry and cursor will then go to
> >> > c3
> >> >
> >> > then they select d5 make an entry and on exiting it will go to gXX and
> >> > so
> >> > on
> >> >
> >> > Cells are paired for example A10 is related to U10m B10 is related to
> >> > W19
> >> > AND SO ON
> >> >
> >> > "Don Guillett" wrote:
> >> >
> >> >> You can do this with a worksheet_change macro in the sheet code.
> >> >> right click sheet tab>view code>left window select worksheet>right
> >> >> select
> >> >> worksheet_change
> >> >>
> >> >> more info needed but this is the basic idea
> >> >>
> >> >> target.offset(30).select
> >> >>
> >> >>
> >> >> --
> >> >> Don Guillett
> >> >> SalesAid Software
> >> >> (E-Mail Removed)
> >> >> "Nigel" <(E-Mail Removed)> wrote in message
> >> >> news:7EE9B253-4207-4F23-BA9F-(E-Mail Removed)...
> >> >> > Is it possible to program so that when a users exits a cell he/she
> >> >> > is
> >> >> > forced
> >> >> > to another cell, ie when leaving d3 the cursor will got to d33
> >> >> >
> >> >> > I know about cell protection etc but that will not do what I would
> >> >> > like
> >> >> >
> >> >> > Thanks
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>