Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
You can do this with a worksheet_change macro in the sheet code.
right click sheet tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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 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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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" wrote in message ... 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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
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" wrote in message ... 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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
I'm still not clear about the third line but you do NOT need to select
anything. If Target.Address = "$A$10" Then Range("c10").interior.colorindex=3 Range("U10").interior.colorindex=6 end if Can be combined into ONE line if both colors are the SAME If Target.Address = "$A$10" Then Range("c10,u10").interior.colorindex=3 -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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" wrote in message ... 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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
Hello Nigel,
I'm afraid I'm still not quite clear on what's happening. It's a bit difficult to tell what's happening with C10 as I can't see the whole code (have you declared the rngPrev somewhere else)? Also do you have any formula in C10? In any case, can I just confirm what you "actually" want to happen rather than what's happening now. As I understand it: If you change the data in A10, you want to select U10 and highlight U10 yellow If you change the data in C10, you want to select W10 and highlight W10 yellow If you change the data in E10, you want to select Y10 and highlight Y10 yellow If you change the data in G10, you want to select AA10 and highlight AA10 yellow If you change the data in I10, you want to select AC10 and highlight AC10 yellow So.... a) Is that correct? b) Is your list restricted to those five ranges or might there by others? Let me know. Best regards John "Nigel" wrote in message ... 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" wrote in message ... 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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
yes they are being declared , there are no formulas in any of the cells,
here is the full color change code Option Explicit Dim rngPrev As Range Dim idxPrev As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'If Target.Count 1 Then Exit Sub Application.ScreenUpdating = False If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev Set rngPrev = Target idxPrev = rngPrev.Interior.ColorIndex rngPrev.Interior.ColorIndex = 3 End Sub if you try that and combine it with the other code you will see what I mean When you enter data in a10 then exit it the cursor will initially move to b10 then onto u10 but it will change the color of b10 and not u10 Thanks if the user enters a value in c10 and hits tab or enter the purpose is to have the focus shift to cell u10 and change the color, "John" wrote: Hello Nigel, I'm afraid I'm still not quite clear on what's happening. It's a bit difficult to tell what's happening with C10 as I can't see the whole code (have you declared the rngPrev somewhere else)? Also do you have any formula in C10? In any case, can I just confirm what you "actually" want to happen rather than what's happening now. As I understand it: If you change the data in A10, you want to select U10 and highlight U10 yellow If you change the data in C10, you want to select W10 and highlight W10 yellow If you change the data in E10, you want to select Y10 and highlight Y10 yellow If you change the data in G10, you want to select AA10 and highlight AA10 yellow If you change the data in I10, you want to select AC10 and highlight AC10 yellow So.... a) Is that correct? b) Is your list restricted to those five ranges or might there by others? Let me know. Best regards John "Nigel" wrote in message ... 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" wrote in message ... 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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Move to a cell
Morning Nigel,
OK, so the reason that B10s color is changing is because of the order in which the events get fired. I've added two Debug lines so it's clear what's going on. Put a Breakpoint on both Debug lines (select the line and press F9) add some data to A10 and tab out of the cell. The code will 'pause' at the first breakpoint and you can then step through one line at a time (using F8). The result is that B10 is selected (which is the normal condition) the Change event fires first on A10 and U10 is then selected. This prompts the first SelectionChange event to be fired on U10 , but importantly before the Change procedure has completed (and the rngPrev becomes B10 as that was the cell that was previously selected). The Change event then finishes off and then the SelectionChange event is fired once more to deal with the B10 selection change. Have a go stepping through and you'll see what I mean. Dim rngPrev As Range Dim idxPrev As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'If Target.Count 1 Then Exit Sub Debug.Print "SelectionChange Event- " & Target.Address Application.ScreenUpdating = True If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev End If Set rngPrev = Target idxPrev = rngPrev.Interior.ColorIndex rngPrev.Interior.ColorIndex = 3 End Sub Private Sub Worksheet_Change(ByVal Target As Range) 'player 1 Debug.Print "Change - Event" & Target.Address 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 Anyway, have a go instead with the code below, Private rngPrev As Range Private idxPrev As Long Private m_SkipChange As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If m_SkipChange = False Then If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev End If Set rngPrev = Target idxPrev = rngPrev.Interior.ColorIndex rngPrev.Interior.ColorIndex = 3 Else m_SkipChange = False End If End Sub Private Sub Worksheet_Change(ByVal Target As Range) 'player 1 With Target If .Address = "$A$10" Then Range("U10").Select m_SkipChange = True Set rngPrev = Range("U10") End If If .Address = "$C$10" Then Range("W19").Select m_SkipChange = True Set rngPrev = Range("W19") End If If .Address = "$E$10" Then Range("Y28").Select m_SkipChange = True Set rngPrev = Range("Y28") End If If .Address = "$G$10" Then Range("AA37").Select m_SkipChange = True Set rngPrev = Range("AA37") End If If .Address = "$I$10" Then Range("AC46").Select m_SkipChange = True Set rngPrev = Range("AC46") End If End With End Sub Best regards John "Nigel" wrote in message ... yes they are being declared , there are no formulas in any of the cells, here is the full color change code Option Explicit Dim rngPrev As Range Dim idxPrev As Long Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'If Target.Count 1 Then Exit Sub Application.ScreenUpdating = False If Not rngPrev Is Nothing Then rngPrev.Interior.ColorIndex = idxPrev Set rngPrev = Target idxPrev = rngPrev.Interior.ColorIndex rngPrev.Interior.ColorIndex = 3 End Sub if you try that and combine it with the other code you will see what I mean When you enter data in a10 then exit it the cursor will initially move to b10 then onto u10 but it will change the color of b10 and not u10 Thanks if the user enters a value in c10 and hits tab or enter the purpose is to have the focus shift to cell u10 and change the color, "John" wrote: Hello Nigel, I'm afraid I'm still not quite clear on what's happening. It's a bit difficult to tell what's happening with C10 as I can't see the whole code (have you declared the rngPrev somewhere else)? Also do you have any formula in C10? In any case, can I just confirm what you "actually" want to happen rather than what's happening now. As I understand it: If you change the data in A10, you want to select U10 and highlight U10 yellow If you change the data in C10, you want to select W10 and highlight W10 yellow If you change the data in E10, you want to select Y10 and highlight Y10 yellow If you change the data in G10, you want to select AA10 and highlight AA10 yellow If you change the data in I10, you want to select AC10 and highlight AC10 yellow So.... a) Is that correct? b) Is your list restricted to those five ranges or might there by others? Let me know. Best regards John "Nigel" wrote in message ... 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" wrote in message ... 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 "Nigel" wrote in message ... 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 tabview codeleft window select worksheetright select worksheet_change more info needed but this is the basic idea target.offset(30).select -- Don Guillett SalesAid Software "Nigel" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically move to next cell... | Excel Worksheet Functions | |||
Automatically move cursor when cell value changes | New Users to Excel | |||
Automatically move cursor when cell value changes | New Users to Excel | |||
Automatically move cursor when cell value changes | New Users to Excel | |||
Move automatically to a cell | Excel Discussion (Misc queries) |