Display selection at upper left corner of spreadsheet

Question

hi is it possible to do this kind of things in excel-2007 vb...

for example user selects cell or range.... let's say cell G13, now this cell will be displayed on the upper-left corner of the sheet (i.e. where usually A1 resides by default) in the case of range selection the upper-left corner of the range will be displayed in upper-left corner of the worksheet...

thanks a lot in advance!

Answer 1

ALT + F11
Double click on ThisWorkbook in the treeview on left (inside VBA).
Paste this code into WorkBook class

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Cells(1, 1).Value = Selection.Cells(1).Address
End Sub

Change Selection.Cells(1).Address to Selection.Cells(1).Value

Note: This will occurr for all sheets inside the workbook. You could change it make it work for a specific sheet by adding a check using the Sh instance in the code.

Answer 2

Here is how you get the spreadsheet to roll to the cell you want:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False            
        ActiveWindow.ScrollColumn = Target.Column
        ActiveWindow.ScrollRow = Target.Row
    Application.EnableEvents = True
End Sub

I think you will find that this will drive the users crazy, but you can probably modify it to get a friendlier effect.