excel - 如果单元格 "A500"在屏幕上可见/则运行 Excel 宏

Question

我想在单元格“A500”后立即运行 Excel 宏
向下/向上滚动工作表时在屏幕上可见。

我记得在某处读过有关 active-x 或标准控件的信息
有一个“滚动到 View 中”事件，所以可以这样做
通过将控件直接放在所需单元格附近的工作表上。
我目前无法找到这个控件。

更好的方法当然是单元格公式，子类化仍然是
我想从长远来看是个坏主意:)

Sub temp_01() 'Excel Vba

'user scrolls down from cell "A1"

'when the user reaches cell "A500" show the following message:
MsgBox "Chapter 2"

End Sub

Answer 1

如上所述，在 Onupdate 事件的帮助下，(捕获鼠标滚轮，而不是单击滚动条)(将 Sheetname(s) 和 Range(s) 更改为您的)

在名为 ClsMonitorOnupdate 的类中:

Option Explicit

Private WithEvents objCommandBars As Office.CommandBars
Private rMonitor As Range
Private scrol As Boolean

Public Property Set Range(ByRef r As Range): Set rMonitor = r: End Property

Public Property Get Range() As Range: Set Range = rMonitor: End Property

Private Sub Class_Initialize()
    Set objCommandBars = Application.CommandBars
End Sub

Private Sub Class_Terminate()
    Set objCommandBars = Nothing
End Sub

Private Sub objCommandBars_OnUpdate()
Dim myrng As Range
    If ActiveWorkbook.Name <> ThisWorkbook.Name Then Exit Sub
    If ActiveSheet.Name <> rMonitor.Parent.Name Then Exit Sub
    If TypeName(Selection) <> "Range" Then Exit Sub
    If Intersect(Selection, rMonitor) Is Nothing Then Exit Sub

    Set myrng = Application.Intersect(ActiveWindow.VisibleRange, ActiveSheet.Range("a500"))

    If Not myrng Is Nothing And Not scrol Then scrol = True: MsgBox "chapter"
    If myrng Is Nothing And scrol Then scrol = False

 End Sub

在 ThisWorkbook 部分:

Option Explicit
Private sRanges As String
Private cMonitor As ClsMonitorOnupdate

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Set cMonitor = Nothing
End Sub
Private Sub Workbook_Open()
  Zetaan ActiveSheet
End Sub
Sub Zetuit()
 Set cMonitor = Nothing
End Sub
Sub Zetaan(sht As Worksheet)
    Select Case sht.Name
        Case "Sheet1": sRanges = "A1:ZZ1000"
        Case "Other Sheet": sRanges = "A1:ZZ1000"
        Case Else: Exit Sub
    End Select
     Set cMonitor = New ClsMonitorOnupdate
    Set cMonitor.Range = Sheets(sht.Name).Range(sRanges)
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Zetaan Sh
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Set cMonitor = Nothing
End Sub