Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
I have a work sheet that has alot of data entry on each row, I would like to
know, when I arrow down to whatever row I want to work on could the entire row auto highlight, and when I move to the next row have that row auto highlight and so on -- Brian Wenstrup |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
I have a work sheet that has alot of data entry on each row, I would like
to know, when I arrow down to whatever row I want to work on could the entire row auto highlight, and when I move to the next row have that row auto highlight and so on Does this macro do what you want? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Right-click the worksheet tab for the sheet you want this functionality on and select View Code from the popup menu. Copy/Paste the above subroutine into the code window that appears. Now, go back to the worksheet and mouse-click, tab, Enter and/or arrow key around the sheet. Rick |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
Chip Pearson has a RowLiner add-in he
http://www.cpearson.com/excel/RowLiner.htm which will do what you want. Hope this helps. Pete On Dec 9, 7:21 pm, Brian Wenstrup wrote: I have a work sheet that has alot of data entry on each row, I would like to know, when I arrow down to whatever row I want to work on could the entire row auto highlight, and when I move to the next row have that row auto highlight and so on -- Brian Wenstrup |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
A slight modification to avoid recursion:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate Application.EnableEvents = True Exit Sub ErrHandler: Application.EnableEvents = True End Sub -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... I have a work sheet that has alot of data entry on each row, I would like to know, when I arrow down to whatever row I want to work on could the entire row auto highlight, and when I move to the next row have that row auto highlight and so on Does this macro do what you want? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Right-click the worksheet tab for the sheet you want this functionality on and select View Code from the popup menu. Copy/Paste the above subroutine into the code window that appears. Now, go back to the worksheet and mouse-click, tab, Enter and/or arrow key around the sheet. Rick |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
It's the middle of the morning, I'm tired and I'll be going to sleep soon,
so that may have something to do with my blind spot at the moment, but what am I missing that will cause the recursion you mention? Granting that you are probably right (so thank you for posting your modification), we can shorten your code by two lines... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub Oh, and earlier I was thinking that if the OP wanted to highlight a group of cells for some reason, he wouldn't be able to unless we restricted the code to single cell selections; so this is probably a better coding for the OP to use... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler If Target.Count 1 Then Exit Sub Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub Rick "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... A slight modification to avoid recursion: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate Application.EnableEvents = True Exit Sub ErrHandler: Application.EnableEvents = True End Sub -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... I have a work sheet that has alot of data entry on each row, I would like to know, when I arrow down to whatever row I want to work on could the entire row auto highlight, and when I move to the next row have that row auto highlight and so on Does this macro do what you want? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Right-click the worksheet tab for the sheet you want this functionality on and select View Code from the popup menu. Copy/Paste the above subroutine into the code window that appears. Now, go back to the worksheet and mouse-click, tab, Enter and/or arrow key around the sheet. Rick |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
what am I missing that will cause the recursion you mention?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Code that 'selects' the entire row triggers the selection_change event, which selects a row, which... Granting that you are probably right (so thank you for posting your modification), we can shorten your code by two lines... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub We can, but I made a considered decision not to post exactly that macro, as the writer may need to add additional code that is not wanted to be run every time. -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... It's the middle of the morning, I'm tired and I'll be going to sleep soon, so that may have something to do with my blind spot at the moment, but what am I missing that will cause the recursion you mention? Granting that you are probably right (so thank you for posting your modification), we can shorten your code by two lines... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub Oh, and earlier I was thinking that if the OP wanted to highlight a group of cells for some reason, he wouldn't be able to unless we restricted the code to single cell selections; so this is probably a better coding for the OP to use... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler If Target.Count 1 Then Exit Sub Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub Rick "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... A slight modification to avoid recursion: Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate Application.EnableEvents = True Exit Sub ErrHandler: Application.EnableEvents = True End Sub -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... I have a work sheet that has alot of data entry on each row, I would like to know, when I arrow down to whatever row I want to work on could the entire row auto highlight, and when I move to the next row have that row auto highlight and so on Does this macro do what you want? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Right-click the worksheet tab for the sheet you want this functionality on and select View Code from the popup menu. Copy/Paste the above subroutine into the code window that appears. Now, go back to the worksheet and mouse-click, tab, Enter and/or arrow key around the sheet. Rick |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
what am I missing that will cause the recursion you mention?
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Code that 'selects' the entire row triggers the selection_change event, which selects a row, which... Are you seeing that on your version of Excel? I just tried it out on my copies of XL2003 and XL2007 and see no evidence of a recursive failure whether I select the row manually or via code; as, for example, with this statement... Range("C3").EntireRow.Select In any event, I would assume my suggestion (for a different reason) to include this statement... If Target.Count 1 Then Exit Sub at the beginning of the subroutine should eliminate this concern; so I am guessing the Error trapping code could be eliminated if this test is performed. Granting that you are probably right (so thank you for posting your modification), we can shorten your code by two lines... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub We can, but I made a considered decision not to post exactly that macro, as the writer may need to add additional code that is not wanted to be run every time. I'm not sure I see where this would be a problem (running Application.EnableEvents=True can't hurt anything even if it were not set to False beforehand and surely we want it to run after it is set to False whether an error is triggered or not); however, I do recognize the benefits of using a consistent code-construction style (Exit Sub followed by error handling code) and so I, of course, will acknowledge that. Rick |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
It doesn't fail, but it does run more than once for a single action. If you
put a breakpoint in, select one cell, and step through, you should see it. Or, here's a more fun example: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Debug.Print Target.Address & " was selected." Target.Offset(1).Select End Sub Or even better :) Private Sub Worksheet_SelectionChange(ByVal Target As Range) Debug.Print Target.Address & " was selected." With Target.Offset(1) .Select .Activate End With End Sub -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... what am I missing that will cause the recursion you mention? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Code that 'selects' the entire row triggers the selection_change event, which selects a row, which... Are you seeing that on your version of Excel? I just tried it out on my copies of XL2003 and XL2007 and see no evidence of a recursive failure whether I select the row manually or via code; as, for example, with this statement... Range("C3").EntireRow.Select In any event, I would assume my suggestion (for a different reason) to include this statement... If Target.Count 1 Then Exit Sub at the beginning of the subroutine should eliminate this concern; so I am guessing the Error trapping code could be eliminated if this test is performed. Granting that you are probably right (so thank you for posting your modification), we can shorten your code by two lines... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub We can, but I made a considered decision not to post exactly that macro, as the writer may need to add additional code that is not wanted to be run every time. I'm not sure I see where this would be a problem (running Application.EnableEvents=True can't hurt anything even if it were not set to False beforehand and surely we want it to run after it is set to False whether an error is triggered or not); however, I do recognize the benefits of using a consistent code-construction style (Exit Sub followed by error handling code) and so I, of course, will acknowledge that. Rick |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Highlight line working on.
Okay, I see what you mean. If you pick a cell (or several cells assuming the
"If Target.Count" test I later proposed is not in use), then that process kicks off the SelectionChange event and, when its code selects the entire row, that, in turn, kicks off another SelectionChange event. On the other hand, if you select the entire row directly, only one SelectionChange event is kicked off as executing the entire row selection in code does nothing to change the selection, so no second SelectionChange event takes place. So, your suggested use of Application.EnableEvents seems like the best way to go here; although its use would only really be significant if other code is to be executed within the SelectionChange event. I don't think the error handler you originally suggested would be needed (at least not for the snippet of code I suggested) as I can't see where the error would be generated from this... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count 1 Then Exit Sub Application.EnableEvents = False Target.EntireRow.Select Target.Activate Application.EnableEvents = True End Sub As I hinted above, though, those Application.EnableEvents statements could be removed, and the subsequent second call to the event procedure tolerated, as doing so would produce no real harm. Executing the two Application.EnableEvents statements might very well be as costly, time-wise, as simply letting the SelectionChange event fire off twice without them. Now, however, if the programmer were to add other code to this procedure, then all bets are off... the Application.EnableEvents, as well as some error handling code, may be required by this additional code in order to protect it. Thanks for your input in this thread (and staying with me through my "dense period"<g); I really appreciated it. Rick "Tim Zych" <tzych@NOSp@mE@RTHLINKDOTNET wrote in message ... It doesn't fail, but it does run more than once for a single action. If you put a breakpoint in, select one cell, and step through, you should see it. Or, here's a more fun example: Private Sub Worksheet_SelectionChange(ByVal Target As Range) Debug.Print Target.Address & " was selected." Target.Offset(1).Select End Sub Or even better :) Private Sub Worksheet_SelectionChange(ByVal Target As Range) Debug.Print Target.Address & " was selected." With Target.Offset(1) .Select .Activate End With End Sub -- Tim Zych SF, CA "Rick Rothstein (MVP - VB)" wrote in message ... what am I missing that will cause the recursion you mention? Private Sub Worksheet_SelectionChange(ByVal Target As Range) Target.EntireRow.Select Target.Activate End Sub Code that 'selects' the entire row triggers the selection_change event, which selects a row, which... Are you seeing that on your version of Excel? I just tried it out on my copies of XL2003 and XL2007 and see no evidence of a recursive failure whether I select the row manually or via code; as, for example, with this statement... Range("C3").EntireRow.Select In any event, I would assume my suggestion (for a different reason) to include this statement... If Target.Count 1 Then Exit Sub at the beginning of the subroutine should eliminate this concern; so I am guessing the Error trapping code could be eliminated if this test is performed. Granting that you are probably right (so thank you for posting your modification), we can shorten your code by two lines... Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error GoTo ErrHandler Application.EnableEvents = False Target.EntireRow.Select Target.Activate ErrHandler: Application.EnableEvents = True End Sub We can, but I made a considered decision not to post exactly that macro, as the writer may need to add additional code that is not wanted to be run every time. I'm not sure I see where this would be a problem (running Application.EnableEvents=True can't hurt anything even if it were not set to False beforehand and surely we want it to run after it is set to False whether an error is triggered or not); however, I do recognize the benefits of using a consistent code-construction style (Exit Sub followed by error handling code) and so I, of course, will acknowledge that. Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In Excel can I automatically highlight the row im working on? | Excel Discussion (Misc queries) | |||
HIGHLIGHT THE ROW I'M WORKING IN | Excel Discussion (Misc queries) | |||
How can I highlight the cell I'm working in? | Setting up and Configuration of Excel | |||
Want to highlight all but first line | New Users to Excel | |||
Want to highlight all but first line | New Users to Excel |