Highlight line working on.

B

Brian Wenstrup

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
 
R

Rick Rothstein \(MVP - VB\)

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
Click to expand...

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
 
T

Tim Zych

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
T

Tim Zych

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
Click to expand...

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
Click to expand...

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.
 
R

Rick Rothstein \(MVP - VB\)

what am I missing that will cause the recursion you mention?
Code that 'selects' the entire row triggers the selection_change event,
which selects a row, which...
Click to expand...

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.

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.
Click to expand...

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
 
T

Tim Zych

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
Top