Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: A Worksheet OnScroll Event ! - How cool is that ?

  1. #1
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,473
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default A Worksheet OnScroll Event ! - How cool is that ?

    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.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  2. #2
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,473
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ? :o

    Regards.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  3. #3
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling, Scotland
    Posts
    73,724
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    0 Thread(s)

    Default

    Jaafar

    Works on 2000.
    If posting code please use code tags.

  4. #4
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,473
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  5. #5
    MrExcel MVP
    Int'l Moderator
    Greg Truby's Avatar
    Join Date
    Jun 2002
    Location
    39 17' 15" N, -94 40' 26" W
    Posts
    9,967
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    You never cease to amaze me, Jafaar!

    FWIW, this is working fine for me in XL2003 SP3
    Greg

    Home: XL 2003, 2007, 2010, and 2013 on Windows 7
    Work: XL 2013, 2016 on Windows 10
    Please use CODE tags - especially for longer excerpts of code.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,473
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  9. #9
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    6,473
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  10. #10
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •