Advance to predetermined cell
 

Not AT all? Then, as Gord Dibben has suggested, you probably haven't put the
code into the proper location. Also, fix that
Case Is = "$B$43"
Range("C43")
to read
Case Is = "$B$43"
Range("C43").Select

as Gord pointed out - the VB engine is going to toss up its hands when it
sees that guy as written <g.

While this is the first time I've tried using Select Case to do this kind of
thing, usually putting some .Intersect tests ahead of it (as Gord is probably
thinking of anyhow) but this seemed a simple solution, and testing here
showed it to work just fine for me - with the code in the proper location.

P.S. the missing e in " 'ignor " will not affect operation, just provides
something for you to pick at for me picking on your missing .Select

Also, if the cells you've listed in your code are the only ones you need to
work with on the sheet at all, look at the suggestion that BoniM made - that
could be a code-less solution for you if that is the case.

"Publius" wrote:

Thanks. But would you mind taking a look at the code and see if you can
decipher what I'm doing wrong. It does not work at all. THANKS!

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case Is = "$B$1"
Range("D31").Select
Case Is = "$D$31"
Range("D33").Select
Case Is = "$D$33"
Range("B38").Select
Case Is = ("$B$38")
Range("C38").Select
Case Is = ("$C$38")
Range("B39").Select
Case Is = ("$C$39")
Range("B40").Select
Case Is = ("$B$40")
Range("C40").Select
Case Is = ("$B$41")
Range("B41").Select
Case Is = "$B$43"
Range ("C43")
Case Else
'ignor anything else
End Select

End Sub

"JLatham" wrote:

Not a worksheet function to do that. But the worksheet_Change() event was
just made for it! Here's a quick piece of code you can adapt easily, Make
sure you use the $ symbols in the addresses tested. There are other ways to
use this event routine to do this kind of thing, but as long as you're
dealing with just a few cells on a sheet this should work ok without being
too time consuming:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case Is = "$C$12"
Range("B14").Select
Case Is = "$D$12", "$E$12"
Range("G11").Select
Case Else
'ignor anything else
End Select

End Sub

To put the code in the right place, right-click on the sheet's tab and
choose [View Code] then cut and paste this into the code sheet presented to
you.

"Publius" wrote:

In Excel 2002 I want to insert a command in certain (not all) cells that once
data is entered therein and "enter" or a hot key is pressed advances the cell
selection to another specific (specified) cell within the worksheet. For
example: type "123" in C-1 and pressing "enter" moves the next cell
selection to E-12. Can anyone tell me how to do this?

Advance to predetermined cell
 

I have posted similar code many times and users seem happy with it without any
"intersect tests" and it works as is.


Gord

On Tue, 9 Jan 2007 17:03:00 -0800, JLatham <HelpFrom @
Jlathamsite.com.(removethis) wrote:

Not AT all? Then, as Gord Dibben has suggested, you probably haven't put the
code into the proper location. Also, fix that
Case Is = "$B$43"
Range("C43")
to read
Case Is = "$B$43"
Range("C43").Select

as Gord pointed out - the VB engine is going to toss up its hands when it
sees that guy as written <g.

While this is the first time I've tried using Select Case to do this kind of
thing, usually putting some .Intersect tests ahead of it (as Gord is probably
thinking of anyhow) but this seemed a simple solution, and testing here
showed it to work just fine for me - with the code in the proper location.

P.S. the missing e in " 'ignor " will not affect operation, just provides
something for you to pick at for me picking on your missing .Select

Also, if the cells you've listed in your code are the only ones you need to
work with on the sheet at all, look at the suggestion that BoniM made - that
could be a code-less solution for you if that is the case.

"Publius" wrote:

Thanks. But would you mind taking a look at the code and see if you can
decipher what I'm doing wrong. It does not work at all. THANKS!

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case Is = "$B$1"
Range("D31").Select
Case Is = "$D$31"
Range("D33").Select
Case Is = "$D$33"
Range("B38").Select
Case Is = ("$B$38")
Range("C38").Select
Case Is = ("$C$38")
Range("B39").Select
Case Is = ("$C$39")
Range("B40").Select
Case Is = ("$B$40")
Range("C40").Select
Case Is = ("$B$41")
Range("B41").Select
Case Is = "$B$43"
Range ("C43")
Case Else
'ignor anything else
End Select

End Sub

"JLatham" wrote:

Not a worksheet function to do that. But the worksheet_Change() event was
just made for it! Here's a quick piece of code you can adapt easily, Make
sure you use the $ symbols in the addresses tested. There are other ways to
use this event routine to do this kind of thing, but as long as you're
dealing with just a few cells on a sheet this should work ok without being
too time consuming:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case Is = "$C$12"
Range("B14").Select
Case Is = "$D$12", "$E$12"
Range("G11").Select
Case Else
'ignor anything else
End Select

End Sub

To put the code in the right place, right-click on the sheet's tab and
choose [View Code] then cut and paste this into the code sheet presented to
you.

"Publius" wrote:

In Excel 2002 I want to insert a command in certain (not all) cells that once
data is entered therein and "enter" or a hot key is pressed advances the cell
selection to another specific (specified) cell within the worksheet. For
example: type "123" in C-1 and pressing "enter" moves the next cell
selection to E-12. Can anyone tell me how to do this?

Advance to predetermined cell
 

Happy Day folks....IT WORKS!!!!

Thanks a bunch...JLatham, Gord, RagDyer, BoniM

Now, can youz guzs help with another one. What is the code to make VB
insert the day's date into the renaming of a worksheet (created by a macro)
and then convert it to text so it remains that date from then on in THAT
worksheet, (while the create macro inserts the current date whenever it is
run)?

Thanks

Delma

"JLatham" wrote:

Not AT all? Then, as Gord Dibben has suggested, you probably haven't put the
code into the proper location. Also, fix that
Case Is = "$B$43"
Range("C43")
to read
Case Is = "$B$43"
Range("C43").Select

as Gord pointed out - the VB engine is going to toss up its hands when it
sees that guy as written <g.

While this is the first time I've tried using Select Case to do this kind of
thing, usually putting some .Intersect tests ahead of it (as Gord is probably
thinking of anyhow) but this seemed a simple solution, and testing here
showed it to work just fine for me - with the code in the proper location.

P.S. the missing e in " 'ignor " will not affect operation, just provides
something for you to pick at for me picking on your missing .Select

Also, if the cells you've listed in your code are the only ones you need to
work with on the sheet at all, look at the suggestion that BoniM made - that
could be a code-less solution for you if that is the case.

"Publius" wrote:

Thanks. But would you mind taking a look at the code and see if you can
decipher what I'm doing wrong. It does not work at all. THANKS!

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case Is = "$B$1"
Range("D31").Select
Case Is = "$D$31"
Range("D33").Select
Case Is = "$D$33"
Range("B38").Select
Case Is = ("$B$38")
Range("C38").Select
Case Is = ("$C$38")
Range("B39").Select
Case Is = ("$C$39")
Range("B40").Select
Case Is = ("$B$40")
Range("C40").Select
Case Is = ("$B$41")
Range("B41").Select
Case Is = "$B$43"
Range ("C43")
Case Else
'ignor anything else
End Select

End Sub

"JLatham" wrote:

Not a worksheet function to do that. But the worksheet_Change() event was
just made for it! Here's a quick piece of code you can adapt easily, Make
sure you use the $ symbols in the addresses tested. There are other ways to
use this event routine to do this kind of thing, but as long as you're
dealing with just a few cells on a sheet this should work ok without being
too time consuming:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address
Case Is = "$C$12"
Range("B14").Select
Case Is = "$D$12", "$E$12"
Range("G11").Select
Case Else
'ignor anything else
End Select

End Sub

To put the code in the right place, right-click on the sheet's tab and
choose [View Code] then cut and paste this into the code sheet presented to
you.

"Publius" wrote:

In Excel 2002 I want to insert a command in certain (not all) cells that once
data is entered therein and "enter" or a hot key is pressed advances the cell
selection to another specific (specified) cell within the worksheet. For
example: type "123" in C-1 and pressing "enter" moves the next cell
selection to E-12. Can anyone tell me how to do this?