+ Reply to Thread
Results 1 to 8 of 8

Change event while sliding scrollbar

  1. #1
    Registered User
    Join Date
    05-09-2013
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    43

    Change event while sliding scrollbar

    I have a form controls scrollbar in my worksheet. I would like to run code not only when the arrows of the scrollbar are clicked but also as the bar is being scrolled without letting up on the mouse button.

    When the bar is left-clicked and slid, I can see that the linked cell is changing, but my change scroll bar code (below) is not running:

    Please Login or Register  to view this content.
    It only runs when you let go of the left mouse button (or single click one of the end arrows). I thought this worksheet change event code would work because the range "myrange" does change as the bar slides.

    Please Login or Register  to view this content.
    But, apparently ranges changed by the scrollbar do not qualify as worksheet changes.

    Anyone see a way to get code to run as the linked cell value of the scrollbar changes?

    thanks

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Change event while sliding scrollbar

    Try linking another cell to the scrollbar's cell, using a simple formula like

    =F2

    Then you should be able to use the worksheet's calculate event.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    05-09-2013
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Change event while sliding scrollbar

    Thanks Bernie. Could you provide sample code? I've tried this code on the worksheet that has the cell I want to use as a trigger.

    Please Login or Register  to view this content.
    But, unexpected things are happening like execution jumping to this sub when a command in an unrelated module to clear contents on an unrelated sheet is executed.

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Change event while sliding scrollbar

    I was mis-remembering how it works - the calculate event does not fire until the mouse is released, so it won't help you. Sorry.

  5. #5
    Registered User
    Join Date
    05-09-2013
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Change event while sliding scrollbar

    I've found there exists a Scrollbar_scroll event in addition to the Scrollbar_change event. But that only seems to work for scrollbar's that are on UserForms.

    For example:

    Please Login or Register  to view this content.
    Does anyone know how to construct a Scrollbar_scroll event that applies to Forms Control Scrollboxes that exist directly in the workbook? Or how to trigger a Worksheet change event when a cell in the worksheet is changed by a scrollbox?

  6. #6
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Change event while sliding scrollbar

    The ActiveX scroll bar has a scroll event that you can use.

  7. #7
    Registered User
    Join Date
    05-09-2013
    Location
    Michigan, USA
    MS-Off Ver
    Excel 2010
    Posts
    43

    Re: Change event while sliding scrollbar

    I never use the ActiveX controls because of the issue with the control box growing in size every time you click it after your laptop has been hooked to a projector. This has been my experience with labels, button, and checkboxes, but I have not tried it yet with scrollbars.

    It seems the Calculate event should work. I can see cells with formulas based on the linked cell changing as the bar is scrolled not only when the mouse button is let up. How does that not qualify as a calculate event?

  8. #8
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,910

    Re: Change event while sliding scrollbar

    I think that the mouse-down puts Excel into a non-macro mode, kind of how Edit mode works. Things are obviously being calculated, but the code is suppressed.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Change ScrollBar Max value when a second ScrollBar vale is changed?
    By Jon Henry in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-11-2013, 06:47 PM
  2. Change Max Value of a Scrollbar
    By jboyd12 in forum Excel General
    Replies: 0
    Last Post: 08-01-2012, 04:32 PM
  3. Auto sort macro for ScrollBar event
    By taimaishu001 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-19-2012, 01:54 AM
  4. Chart with scrollbar that offsets the period. Can it have event as well?
    By c.vaibhav in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 11-11-2011, 10:30 AM
  5. Worksheet Change event ignore change event
    By jomili in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-07-2011, 12:29 PM
  6. Userform Scrollbar Event
    By vin1 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-30-2011, 03:32 AM
  7. Worksheet Change Event-change event to trigger
    By Steph in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-18-2005, 06:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1