A Worksheet OnScroll Event ! - How cool is that ?

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
9,577
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

There is no such event in excel. Using a timer to constantly monitor any change of the ScrollRow/ScrollColumn of the activewindow is feasible but it is not worth it because of the strain that such a running timer would put on the application.

While playing around with some Controls via the "More Controls" icon on the ToolBox Toolbar, I came accross this ActiveX control named: InkPicture which has an interesting event called InkPicture_Painted and which basically fires every time it receives a repaint message.

I thought , maybe if i place one on the worksheet , the Control paint event would fire when scrolling the worksheet. Guess what: It worked :)

I embeeded one into the worksheet and reduced its width to a minimum so that it's almost invisible. I streched its height over 1000 rows so it covers a reasonable down/scrolling region.

Ok. Now, before I get too excited about this, there is a problem: Does this InkPicture Control come with all or most Office standard installations like the OWC controls? if so then great. if not, i should just forget about this whole thing. (i am running Office XP XL 2003)

Notice that there is also an annoying prompt upon opening a workbook containing an embeeded ActiveX control. Fortunatly, this can be avoided by adding a reference to the Control library ( MS Tablet PC Type Lib) Programatically , adding the Control at run time and hooking its events in a Class module.

here is a a workbook demo that shows an implementation of this custom Worksheet Scroll Event (it captures the event to prevent the user from scrolling down beyond the visible range ) : http://www.savefile.com/files/1158486

I would appreciate any feedback on this as i would like to know if it works on different machines/XL versions.

Regards.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Bump!

I don't have a different PC within reach to give this a shot at the moment.

Has anyone here tried the workbook demo ? :eek:

Regards.
 
Upvote 0
Works on 2000.
Well, that's encouraging :) - Apparently, this control come with any standard MS Office installation which makes this idea of using it to capture the worksheet scroll Event more reliable and portable.

I'll now see if I can add the control at run time so as to avoid the annoying security prompt at the opening of the workbook.

Thanks very much Norie.

Regards.
 
Upvote 0
Works on Vista / Office 2000, XP, and 2003. I could not figure out the installation standards for InkObj.dll. Mine was installed as part of the VS SDK.
 
Upvote 0
Hi all,

I am trying to add this InkPic control at runtime and trap its events in a class module using the codes below.

The code looks correct to me but when I run the "EnableScroll" Sub I get the runtime error 13 " Incompatible type " .
The error fires on the line :"Set oNewPicClass.InkPicEvents = oCtrl" located inside the "HookPicEvents" Sub.

here is the code that goes in a Standard Module:
Code:
Option Explicit

Private oNewPicClass As InkPicEventClass
Private sMsg As String

Public oCtrl As Object
Public dTimer As Double
Public RangeInView As Range
Public bDisableScroll As Boolean


Sub EnableScroll()

    Call AddCtrl
    Call HookPicEvents
    bDisableScroll = False
    Application.StatusBar = False

End Sub

Sub DisableScroll()

    bDisableScroll = True
    Set RangeInView = ActiveWindow.VisibleRange
    oCtrl.Delete
    
End Sub


Private Sub AddCtrl()

    Set oCtrl = ActiveSheet.OLEObjects.Add _
    (ClassType:="msinkaut.InkPicture.1", Left:=0, Top:=0, Width:=0.01, _
    Height:=0.01)
    oCtrl.ShapeRange.ScaleWidth 0.01, 0, 1
    oCtrl.ShapeRange.ScaleHeight 3.38, 0, 1
      
End Sub

Private Sub OnTimeProc()

    On Error Resume Next
    If Timer - dTimer <= 0.1 Then
        sMsg = "Scrolling the worksheet is disabled !" & vbCrLf & vbCrLf & _
        "You can only naviguate within" & vbCrLf & "the visible range :" _
        & RangeInView.Address
        MsgBox sMsg, vbCritical
        Application.StatusBar = False
    End If

End Sub

Private Sub HookPicEvents()

    Set oNewPicClass = New InkPicEventClass
    Set oNewPicClass.InkPicEvents = oCtrl
End Sub


here is the code of the Class Module named : "InkPicEventClass"

Code:
Public WithEvents InkPicEvents As InkPicture


Private Sub InkPicEvents_Painted(ByVal hDC As Long, ByVal Rect As MSINKAUTLib.IInkRectangle)

    On Error Resume Next
    If bDisableScroll Then
        Application.StatusBar = "Srcrolling "
        If ActiveWindow.VisibleRange.Cells(1, 1).Address <> _
            RangeInView.Cells(1, 1).Address Then
            Application.Goto RangeInView.Cells(1, 1), True
            ActiveSheet.Select RangeInView.Cells(1, 1)
            dTimer = Timer
            Application.OnTime Now + TimeSerial(0, 0, 0.1), "OnTimeProc"
        End If
    End If

End Sub

Any thoughts why I may be getting such an error ?

Again, thanks everyone for the feedback ?

Regards.
 
Upvote 0
oCtrl is type OleObject. You need the actual object reference contained within the OleObject wrapper...

Change:
Set oNewPicClass.InkPicEvents = oCtrl
To:
Set oNewPicClass.InkPicEvents = oCtrl.Object
 
Upvote 0
oCtrl is type OleObject. You need the actual object reference contained within the OleObject wrapper...

Change:
Set oNewPicClass.InkPicEvents = oCtrl
To:
Set oNewPicClass.InkPicEvents = oCtrl.Object


Tom. I forgot to mention that I had already tried that before but doesn't work either. when "oCtrl.Object" is used, there is no error generated but the event handler just doesn't fire (execute) at all ! Strange !

Regards.
 
Upvote 0
I don't know then. Maybe events are sourced differently on the platform this was created for?
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top