Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Advance to predetermined cell | Excel Worksheet Functions | |||
Using an offset formula for the reference in a relative reference | Excel Worksheet Functions | |||
insert date | Excel Worksheet Functions | |||
Urgent date/scheduling calc needed | Excel Worksheet Functions | |||
cell color index comparison | New Users to Excel |