Highlight row the cursor is in? 2

[calvinb]

I have an Excel 2010 sheet with a lot of checkboxes on each row. The user has to type some info in the left side and then move across the row to check some of the checkboxes. The problem is that it's easy to get up or down a row and therefore check the checkbox on the wrong row.

I would like to have the row highlighted somehow depending on where the mouse pointer or cursor is to guide the user across the correct row. Is this possible?

It can be done by clicking the row number on the left of the screen but I want to hide them from the user because I have my own numbers in Column A and they get confused with the Row numbers.

Thanks.

 

[combo]

There are some different ways to accomplish this:
1) keyboard shortcut: [SHIFT]+[SPACE],
2) keyboard navigation: [CTRL]+[left arrow] (may need to be repeated)
3) worksheet layout: place controls on the left side and freeze a part of sheet,
4) code: use SelectionChange event

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
With ActiveCell
    .EntireRow.Select
    .Activate
End With
Application.EnableEvents = True
End Sub

5) code: any change of selection activated by macro assigned to key (basing on the With ... End With structure above).

combo

 

[SkipVought]

You might want to check out the Structured Table feature in Excel 2007+: Insert > Tables > Table.

With this feature you can TAB after entering data and it will track along the row, until the last column of the table, at which point the next TAB takes you to the FIRST column of the next row of the table.

I would say to your users, "Use Excel as it is designed!" I think that is it really handy that ENTER moves my selection to the next row and TAB moves my selection to the next column. I THINK about what I want to do and ACT accordingly.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[HughLerwill]

This current blog item may be of interest;

http://www.dailydoseofexcel.com/archives/2012/03/21/highlight-row-and-column-of-active-cell/

 

[calvinb]

Thanks Combo. That works exactly like I wanted. I have one co-worker who doesn't like it so I was wondering if there is a way to turn it on and off... say with a checkbox or button. I tried today with an existing checkbox on the page but couldn't get it to work.

Thanks Skip. I'll check that out!

 

[combo]

With (activex) checkbox on the sheet, default false (row selection off):

Public WithEvents xlWorksheet As Worksheet

Private Sub CheckBox1_Click()
If Me.CheckBox1.Value = True Then
    Set xlWorksheet = Me
    Call xlWorksheet_SelectionChange(ActiveCell)
Else
    Set xlWorksheet = Nothing
    ActiveCell.Select
End If
End Sub

Private Sub xlWorksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
With Target.Cells(1)
    .EntireRow.Select
    .Activate
End With
Application.EnableEvents = True
End Sub

combo

 

[calvinb]

Thank you, Combo! That works fine.

Much appreciated!!