| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move to SELECTED Cells
I got this macro from this newsgroup (thank you) that
enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Move to SELECTED Cells
It doesn't have anything to do with whether the cell contains an entry or
not. The change event only fires when the user finishes editing a cell. The can make a change or not - but if they are in edit mode and leave it, then the change event fires. Other than that, your macro would not have any effect. If you want the kind of control you state, you would also need to use the selectionchange event and use a static variable to store the last selection, then if it meets one of your criteria, move the selection to the appropropriate cell (possibly disabling events so you don't get a recursive call). Have you tried unlocking the cells where you want entries and protecting the sheet. If that won't give you the order you want, then perhaps you need to think about redesigning the layout of your sheet. -- Regards, Tom Ogilvy "Danny" wrote in message ... I got this macro from this newsgroup (thank you) that enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Move to SELECTED Cells
I unlocked all selected cells and protected the sheet. I
am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. Can you edit my macro so if I press ENTER, it will move to the next SELECTED cell regardless if I edit the cell or not (like pressing the TAB key to move to the next selected cell)? Thanks in advance. -----Original Message----- It doesn't have anything to do with whether the cell contains an entry or not. The change event only fires when the user finishes editing a cell. The can make a change or not - but if they are in edit mode and leave it, then the change event fires. Other than that, your macro would not have any effect. If you want the kind of control you state, you would also need to use the selectionchange event and use a static variable to store the last selection, then if it meets one of your criteria, move the selection to the appropropriate cell (possibly disabling events so you don't get a recursive call). Have you tried unlocking the cells where you want entries and protecting the sheet. If that won't give you the order you want, then perhaps you need to think about redesigning the layout of your sheet. -- Regards, Tom Ogilvy "Danny" wrote in message ... I got this macro from this newsgroup (thank you) that enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. . |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Move to SELECTED Cells
Private Sub Worksheet_Change(ByVal Target As Range)
Application.MoveAfterReturnDirection = xlToLeft me.EnableSelection = xlUnlockedCells If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub -- Regards, Tom Ogilvy "Danny" wrote in message ... I unlocked all selected cells and protected the sheet. I am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. Can you edit my macro so if I press ENTER, it will move to the next SELECTED cell regardless if I edit the cell or not (like pressing the TAB key to move to the next selected cell)? Thanks in advance. -----Original Message----- It doesn't have anything to do with whether the cell contains an entry or not. The change event only fires when the user finishes editing a cell. The can make a change or not - but if they are in edit mode and leave it, then the change event fires. Other than that, your macro would not have any effect. If you want the kind of control you state, you would also need to use the selectionchange event and use a static variable to store the last selection, then if it meets one of your criteria, move the selection to the appropropriate cell (possibly disabling events so you don't get a recursive call). Have you tried unlocking the cells where you want entries and protecting the sheet. If that won't give you the order you want, then perhaps you need to think about redesigning the layout of your sheet. -- Regards, Tom Ogilvy "Danny" wrote in message ... I got this macro from this newsgroup (thank you) that enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. . |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Move to SELECTED Cells
Tom,
Thank you for your response. With the macro below, after pressing enter, it won't move to the next selected cell unless you edit the cell. Regards, Danny -----Original Message----- Private Sub Worksheet_Change(ByVal Target As Range) Application.MoveAfterReturnDirection = xlToLeft me.EnableSelection = xlUnlockedCells If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub -- Regards, Tom Ogilvy "Danny" wrote in message ... I unlocked all selected cells and protected the sheet. I am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. Can you edit my macro so if I press ENTER, it will move to the next SELECTED cell regardless if I edit the cell or not (like pressing the TAB key to move to the next selected cell)? Thanks in advance. -----Original Message----- It doesn't have anything to do with whether the cell contains an entry or not. The change event only fires when the user finishes editing a cell. The can make a change or not - but if they are in edit mode and leave it, then the change event fires. Other than that, your macro would not have any effect. If you want the kind of control you state, you would also need to use the selectionchange event and use a static variable to store the last selection, then if it meets one of your criteria, move the selection to the appropropriate cell (possibly disabling events so you don't get a recursive call). Have you tried unlocking the cells where you want entries and protecting the sheet. If that won't give you the order you want, then perhaps you need to think about redesigning the layout of your sheet. -- Regards, Tom Ogilvy "Danny" wrote in message ... I got this macro from this newsgroup (thank you) that enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. . . |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Move to SELECTED Cells
Tom,
I'm sorry, when you press enter, it moves to the left, NOT to the NEXT SELECTED Cells. Thanks, Danny -----Original Message----- Tom, Thank you for your response. With the macro below, after pressing enter, it won't move to the next selected cell unless you edit the cell. Regards, Danny -----Original Message----- Private Sub Worksheet_Change(ByVal Target As Range) Application.MoveAfterReturnDirection = xlToLeft me.EnableSelection = xlUnlockedCells If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub -- Regards, Tom Ogilvy "Danny" wrote in message .. . I unlocked all selected cells and protected the sheet. I am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. Can you edit my macro so if I press ENTER, it will move to the next SELECTED cell regardless if I edit the cell or not (like pressing the TAB key to move to the next selected cell)? Thanks in advance. -----Original Message----- It doesn't have anything to do with whether the cell contains an entry or not. The change event only fires when the user finishes editing a cell. The can make a change or not - but if they are in edit mode and leave it, then the change event fires. Other than that, your macro would not have any effect. If you want the kind of control you state, you would also need to use the selectionchange event and use a static variable to store the last selection, then if it meets one of your criteria, move the selection to the appropropriate cell (possibly disabling events so you don't get a recursive call). Have you tried unlocking the cells where you want entries and protecting the sheet. If that won't give you the order you want, then perhaps you need to think about redesigning the layout of your sheet. -- Regards, Tom Ogilvy "Danny" wrote in message ... I got this macro from this newsgroup (thank you) that enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. . . . |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Move to SELECTED Cells
I unlocked all selected cells and protected the sheet. I
am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. The two commands I added will make the enter key work like the tab key. That is what you asked. It doesn't fire (i.e. the settings are not made) until you edit one cell. You could put it (those commands, appropriately modified) in the sheet activate event or the workbook open event. -- Regards, Tom Ogilvy "Danny" wrote in message ... Tom, I'm sorry, when you press enter, it moves to the left, NOT to the NEXT SELECTED Cells. Thanks, Danny -----Original Message----- Tom, Thank you for your response. With the macro below, after pressing enter, it won't move to the next selected cell unless you edit the cell. Regards, Danny -----Original Message----- Private Sub Worksheet_Change(ByVal Target As Range) Application.MoveAfterReturnDirection = xlToLeft me.EnableSelection = xlUnlockedCells If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub -- Regards, Tom Ogilvy "Danny" wrote in message .. . I unlocked all selected cells and protected the sheet. I am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. Can you edit my macro so if I press ENTER, it will move to the next SELECTED cell regardless if I edit the cell or not (like pressing the TAB key to move to the next selected cell)? Thanks in advance. -----Original Message----- It doesn't have anything to do with whether the cell contains an entry or not. The change event only fires when the user finishes editing a cell. The can make a change or not - but if they are in edit mode and leave it, then the change event fires. Other than that, your macro would not have any effect. If you want the kind of control you state, you would also need to use the selectionchange event and use a static variable to store the last selection, then if it meets one of your criteria, move the selection to the appropropriate cell (possibly disabling events so you don't get a recursive call). Have you tried unlocking the cells where you want entries and protecting the sheet. If that won't give you the order you want, then perhaps you need to think about redesigning the layout of your sheet. -- Regards, Tom Ogilvy "Danny" wrote in message ... I got this macro from this newsgroup (thank you) that enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. . . . |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Move to SELECTED Cells
Tom,
The glitz was my mistake. C2 is unlocked, however, I didn't realize that when I was making the form I merged C2 and C3 (locked). After unlocking the merged cells, the two commands you provided works perfectly. Thank you so much for your time and patience. Have a great weekend. Regards, Danny -----Original Message----- I unlocked all selected cells and protected the sheet. I am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. The two commands I added will make the enter key work like the tab key. That is what you asked. It doesn't fire (i.e. the settings are not made) until you edit one cell. You could put it (those commands, appropriately modified) in the sheet activate event or the workbook open event. -- Regards, Tom Ogilvy "Danny" wrote in message ... Tom, I'm sorry, when you press enter, it moves to the left, NOT to the NEXT SELECTED Cells. Thanks, Danny -----Original Message----- Tom, Thank you for your response. With the macro below, after pressing enter, it won't move to the next selected cell unless you edit the cell. Regards, Danny -----Original Message----- Private Sub Worksheet_Change(ByVal Target As Range) Application.MoveAfterReturnDirection = xlToLeft me.EnableSelection = xlUnlockedCells If Target.Address = "$C$2" Then Range("G4").Select If Target.Address = "$G$4" Then Range("C6").Select If Target.Address = "$C$6" Then Range("E6").Select 'Etc, etc, etc... End Sub -- Regards, Tom Ogilvy "Danny" wrote in message .. . I unlocked all selected cells and protected the sheet. I am just wondering that if I use the TAB key, it works. It also works when you EDIT a cell and press ENTER. Can you edit my macro so if I press ENTER, it will move to the next SELECTED cell regardless if I edit the cell or not (like pressing the TAB key to move to the next selected cell)? Thanks in advance. -----Original Message----- It doesn't have anything to do with whether the cell contains an entry or not. The change event only fires when the user finishes editing a cell. The can make a change or not - but if they are in edit mode and leave it, then the change event fires. Other than that, your macro would not have any effect. If you want the kind of control you state, you would also need to use the selectionchange event and use a static variable to store the last selection, then if it meets one of your criteria, move the selection to the appropropriate cell (possibly disabling events so you don't get a recursive call). Have you tried unlocking the cells where you want entries and protecting the sheet. If that won't give you the order you want, then perhaps you need to think about redesigning the layout of your sheet. -- Regards, Tom Ogilvy "Danny" wrote in message ... I got this macro from this newsgroup (thank you) that enables the user to move to selected cells by filling up the cell or use the tab key: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$C$2" Then Range ("G4").Select If Target.Address = "$G$4" Then Range ("C6").Select If Target.Address = "$C$6" Then Range ("E6").Select 'Etc, etc, etc... End Sub However, if the active cells are already filled up and the operator uses the ENTER key to move to the next cell, it doesn't work. Let's say the active cell is C2 (previously filled-up). If you press enter, the active cell will be C3 instead of G4. Please help. . . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple cells or columns are selected instead of selected cell or | Excel Discussion (Misc queries) | |||
| Move selected Text/Numbers from Col D to Col A/B | Excel Discussion (Misc queries) | |||
| Move only around selected cells?? | Excel Discussion (Misc queries) | |||
| I just selected Move or Copy from Edit | Excel Discussion (Misc queries) | |||
| highlight selected row permanently until u move out from the row | Excel Discussion (Misc queries) | |||
Linear Mode
