+ Reply to Thread
Results 1 to 3 of 3

Scroll bar with a mid point of 0 and positive increasing values on both ends

  1. #1
    Registered User
    Join Date
    06-30-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    29

    Scroll bar with a mid point of 0 and positive increasing values on both ends

    Hi all,

    I've got an excel sheet that lets people choose between 2 choices. I'm using scroll bars to make it easy for them to choose and visualize their preference with as little mouse clicks as possible. The scroll bar is only one sided though, meaning it starts from a min value to a max value.

    I would like a scroll bar with a of 0, with the value increasing positively as the user scrolls to the left, and the value also increasing positively as it scrolls to the right.

    As far as I have searched there is no formula or VBA code that allows me to do so.

    Any help is much appreciated.

    Cheers,
    Jason
    Attached Files Attached Files

  2. #2
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,640

    Re: Scroll bar with a mid point of 0 and positive increasing values on both ends

    Hi Jason,

    I made only changes to first scroll bar, you can apply similar methodology for the next pieces.

    1st changed range for first scroll bar - as you posted it stops in places not corresponding full cells. so now it is min 1 max 11.

    To show "by how much you can use difference between midpoint and real value and take ABS of it.

    To have it stepped as you suggested - 9,7,5,3,1, 0, 1,3,5,7,9 it is not enough to use just
    =ABS(6-H3)
    because the above would return 5,4,3,2,1,0,1,2,3,4,5

    But one can use
    =2*ABS(6-H3)-(H3<>6)

    First part is obvious - normally you want steps by 2:10,8,6,4,2,0,2,4... . And the second part of above H3<>6 will evaluate as true/false, but excel will do a conversion to 1/0 as it is used in arithmetics so you will end up with required sequence.

    and to show what is the preference you can use
    =IF(H3=6,"equal",IF(H3<6,"A","B"))

    see attached.
    Attached Files Attached Files
    Best Regards,

    Kaper

  3. #3
    Registered User
    Join Date
    06-30-2012
    Location
    Singapore
    MS-Off Ver
    Excel 2010
    Posts
    29

    Re: Scroll bar with a mid point of 0 and positive increasing values on both ends

    Perfect!!!

+ 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. [SOLVED] Setting a range that ends where data ends?
    By Mgassma in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-02-2013, 09:46 AM
  2. Scroll bar (Form Control) - Resetting the scroll bar to particular values
    By Vishb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-29-2011, 04:10 AM
  3. Scroll Bar - negative to positive range?
    By tlapointe1970 in forum Excel General
    Replies: 1
    Last Post: 04-27-2011, 11:04 PM
  4. scroll through worksheets - starting point
    By mwc48910 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-26-2011, 10:00 AM
  5. +(-)ve values ends in Dr(Cr)
    By sasikumar in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-25-2006, 10:35 AM

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