A Worksheet OnScroll Event ! - How cool is that ?

[Jaafar Tribak]

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.

 

[Jaafar Tribak]

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 ?

Regards.

 

[Norie]

Jaafar

Works on 2000.

 

[Jaafar Tribak]

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.

 

[Greg Truby]

You never cease to amaze me, Jafaar!

FWIW, this is working fine for me in XL2003 SP3

 

[Legacy 98055]

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.

 

[Jaafar Tribak]

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:

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"

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.

 

[Legacy 98055]

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

 

[Jaafar Tribak]

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.

 

[Legacy 98055]

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