VBA to center Selected Cell in Current Window
Thanks Steve!
"Steve Yandl" wrote:
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
wrote in message
.. .
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
|