VBA to center Selected Cell in Current Window
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 |
VBA to center Selected Cell in Current Window
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 |
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 |
All times are GMT +1. The time now is 05:33 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com