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

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

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