VBA to center Selected Cell in Current Window

[EagleOne]

2003/2007

What VBA code can I use to center the selected cell in the current window?


Programmatically, I have selected a cell and that cell is in the current window for the 500+ sheets
in a workbook.

BTW, the code below is from Automation of Excel from Access. Therefore, the standard Excel objects
Application, ActiveSheet, Acvtiveworkbook are oSht, oWbk1 & oXL respectively.

The code I used is next:

For Each oSht In oWbk1.Worksheets
oSht.Activate
oXL.ActiveWindow.View = xlNormalView
oXL.ActiveWindow.Zoom = 75
oSht.Columns.AutoFit
oSht.ActiveCell.Select
oSht.Protect _
DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True

Next oSht

I would like to have that selected cell be in the middle of that screen no matter how many rows
there are in the worksheet.

TIA EagleOne

 

[Steve Yandl]

EagleOne,

Try a modivication of this:

'-------------------------------------

Dim rHot As Long
Dim cHot As Long

rHot = ActiveCell.Row
cHot = ActiveCell.Column

With ActiveWindow
If rHot > Int(.VisibleRange.Rows.Count * 0.5) - 1 Then
.scrollRow = rHot - Int(.VisibleRange.Rows.Count * 0.5) + 1
End If
If cHot > Int(.VisibleRange.Columns.Count * 0.5) - 1 Then
.ScrollColumn = cHot - Int(.VisibleRange.Columns.Count * 0.5) + 1
End If
End With

'------------------------------------

Steve Yandl