Excel VBA 自动滚动屏幕
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24646787/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA scrollscreen automatically
提问by user3819090
Let's say the range (A1:A200)contains a descending order of numbers (from 200 to 1) and I have implemented conditional formatting on the range.
假设该范围(A1:A200)包含数字的降序排列（从 200 到 1），并且我已对该范围实施了条件格式。
When any cell in A1:A200 matches the source data (Cell B1), which is continuously updated, it will be highlighted in Yellow in its background.
当 A1:A200 中的任何单元格与源数据匹配时，源数据(Cell B1)不断更新，它将在其背景中以黄色突出显示。
My question is How can I write a code to scroll the screen to the matched cell(highlighted yellow) and position it in the middle of my screen?
我的问题是如何编写代码将屏幕滚动到匹配的单元格（突出显示为黄色）并将其放置在屏幕中间？
I don't want the ".select"function as the matched cell is keep changing and the target cell may not be the active cell. Much appreciate if anyone can offer some help to me. Many thanks!
我不想要该".select"功能，因为匹配的单元格不断变化并且目标单元格可能不是活动单元格。如果有人可以为我提供一些帮助，将不胜感激。非常感谢！
回答by djikay
Adapted the following code from this link. I modified the original code to notselect the cell we scroll to, as you specified. I've tested it with Excel 2010 and it works for me.
改编自此链接的以下代码。我修改了原始代码以不选择我们滚动到的单元格，如您指定的那样。我已经用 Excel 2010 对其进行了测试，它对我有用。
Sub ScrollToCell(OnCell As Range)
Dim VisRows As Integer
Dim VisCols As Integer
Application.ScreenUpdating = False
'
' Switch over to the OnCell's workbook and worksheet.
'
OnCell.Parent.Parent.Activate
OnCell.Parent.Activate
'
' Get the number of visible rows and columns for the active window.
'
With ActiveWindow.VisibleRange
VisRows = .Rows.Count
VisCols = .Columns.Count
End With
'
' Now, determine what cell we need to scroll to. The ScrollRow and
' ScrollColumn methods will place that cell reference in the upper left
' corner of the screen, so that reference needs to be VisRows/2 above and
' VisCols/2 columns to the left of the cell we want to center on.
' Use the Max function to ensure we're not trying to scroll a cell in
' row <=0 or column <=0.
'
ActiveWindow.ScrollRow = Application.WorksheetFunction.Max(1, OnCell.Row + (OnCell.Rows.Count / 2) - (VisRows / 2))
ActiveWindow.ScrollColumn = Application.WorksheetFunction.Max(1, OnCell.Column + (OnCell.Columns.Count / 2)) - Application.WorksheetFunction.RoundDown((VisCols / 2), 0)
Application.ScreenUpdating = True
End Sub
To call it, you can do:
要调用它，您可以执行以下操作：
CenterOnCell Range("S50")
I'm sure you can now adapt this code to suit your own specific requirements.
我相信您现在可以修改此代码以满足您自己的特定要求。
