Click here for important information about Computing.net.

How to put focus on specifc cells

Tags:
Microsoft Microsoft excel 2007 full vers...
excel
alternate
November 14, 2010 at 12:02:39
Specs: Windows 7, ?
Excel 2007
Spread sheet with 315 rows. Need to select from data validation dropdown lists in column C and column D. It take the user 3 clicks to move focus to the cell, initiate the data validation dropdown, and make a selection.

Is there some way (Excel or VBA) to automatically put focus on the next cell, eliminating one of the three needed clicks? Seems trivial but having to do 3 clicks for 630 cells gets tedious very quickly and if there is a way to cut that down it would make the users VERY happy.

Thanks,

Fergie


See More: How to put focus on specifc cells

Report •

#1
November 15, 2010 at 17:34:42
Right click the sheet tab for the sheet with the drop downs and paste this code into the pane that opens.

Whenever a change is made to Column C, the cell in Column D, same row will be selected.

As written, it works for the entire column. If you need it to work for only a specific range, see the alternate code for an example on how to limit it to just a specific range.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Column = 3 Then Target.Offset(0, 1).Select
End Sub

Alternate, for a specific range:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("C1:C300")) Is Nothing _
   Then Target.Offset(0, 1).Select
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
November 15, 2010 at 18:36:33
I messed with that for hours and misunderstood the concept... I'm shocked that it was that simple. I can add another IF to push the pointer to the next line and back to column C.... Super! Thank you very much.

Report •
Start a discussion
Related Solutions
See More ↓


Ask Question