Results 1 to 8 of 8

Thread: 1, 2 skip a few 99, 100 scenarios
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular GomaPile's Avatar
    Join Date
    Jul 2006
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question 1, 2 skip a few 99, 100 scenarios

    Hello All,

    I have a simple request, if that someone could kindly please write a vba code. I have a single scroll bar (ActiveX control) that is connected to H25 cell; to which it goes up increment levels by one with every mouse click, starting from 1 194.

    What I like to achieve at some point of the increment levels that changes, I like is 1, 2 skip a few 99, 100 scenarios ( from a certain range of numbers that I don't need, missing them altogether) going to the next number and returning back to 1.

    For example:
    1
    2
    3
    4
    5 to 9 skip these numbers goto 10
    10
    11
    12
    13 to 19 skip these numbers goto 20
    20
    21
    22
    23
    24 to 27 skip these numbers goto 28
    28
    29
    30 to 194 skip these numbers goto 1


    Cheers 😊
    Goma (NASA)

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,648
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: 1, 2 skip a few 99, 100 scenarios

    Something like this might work for you. Set the .Max of the scroll bar to 30
    Code:
    Private Sub ScrollBar1_Change()
        With ScrollBar1
            Select Case .Value
                Case Is < 5
                    rem do nothing
                Case Is < 10
                    .Value = 10
                Case Is < 13
                    Rem Do Nothing
                Case Is < 20
                    .Value = 20
                Case Is < 24
                    Rem do nothing
                Case Is < 30
                    .Value = 1
            End Select
        End With
        
    End Sub

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,408
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: 1, 2 skip a few 99, 100 scenarios

    Quote Originally Posted by mikerickson View Post
    Something like this might work for you. Set the .Max of the scroll bar to 30
    Perhaps it was just an oversight that this code prevents the allowable values of 28 and 29 but a scroll bar can usually scroll either way and this code prevents scrolling down past any of the trigger points (eg 10) that you get above, at least by clicking the scroll bar arrows.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,648
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: 1, 2 skip a few 99, 100 scenarios

    Good catch
    Code:
    Dim lastScrollValue As Long
    Dim DisableMyEvents As Boolean
    
    Private Sub ScrollBar1_Change()
        If DisableMyEvents Then Exit Sub
        DisableMyEvents = True
        With ScrollBar1
            If .Value <= lastScrollValue Then
                Select Case .Value
                    Case Is < 1
                        .Value = (.Max - 1)
                    Case Is < 4
                        Rem do nothing
                    Case Is < 10
                        .Value = 4
                    Case Is < 12
                        Rem Do Nothing
                    Case Is < 20
                        .Value = 12
                    Case Is < 24
                        Rem do nothing
                    Case Is < 28
                        .Value = 24
                    Case Is < 30
                        Rem do nothing
                End Select
            Else
            Select Case .Value
                    Case Is < 5
                        Rem do nothing
                    Case Is < 10
                        .Value = 10
                    Case Is < 13
                        Rem Do Nothing
                    Case Is < 20
                        .Value = 20
                    Case Is < 24
                        Rem do nothing
                    Case Is < 30
                        .Value = 1
                End Select
            End If
        End With
    
        lastScrollValue = ScrollBar1.Value
        DisableMyEvents = False
       
    End Sub
    Last edited by mikerickson; Aug 19th, 2018 at 02:41 AM.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,408
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: 1, 2 skip a few 99, 100 scenarios

    Assuming the scroll bar max & min values have been set in its properties, this might also do the job?
    Seems to cycle through the disallowed values fast enough to me.
    Code:
    Private Sub ScrollBar1_Change()
      Static lastScrollValue As Long
      
      Application.ScreenUpdating = False
      With ScrollBar1
        Select Case .Value
          Case .Max
            .Value = .Min
          Case 5 To 9, 13 To 19, 24 To 27, 30 To 194
            .Value = .Value + IIf(.Value > lastScrollValue, 1, -1)
        End Select
        lastScrollValue = .Value
      End With
      Application.ScreenUpdating = True
    End Sub
    Last edited by Peter_SSs; Aug 19th, 2018 at 03:23 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    Board Regular GomaPile's Avatar
    Join Date
    Jul 2006
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1, 2 skip a few 99, 100 scenarios

    Hello gentleman Mikerickson & Peter,

    As always, I appreciate your support and thank you gentlemen for the time to help me once again. Still today I’m using your vba codes that you made many years ago for my workplace.

    All 3 vba codes below work fine and they do exactly what I need. No defence to you mikerickson – both of your codes do what they’re meant to do, though your first one it wouldn’t allow me to click backwards but the second code did give the option to move back & forth. If it ok with you, I like prefer to go with Peter’s version, its simple to edit as required. But I do have plans on your second version for a questionnaire project.

    Quote Originally Posted by mikerickson View Post
    Something like this might work for you. Set the .Max of the scroll bar to 30
    Code:
    VBA Code 2 Private Sub ScrollBar1_Change()

    Quote Originally Posted by mikerickson View Post
    Good catch
    Code:
    vba Code 2 Private Sub ScrollBar1_Change()

    Quote Originally Posted by Peter_SSs View Post
    Assuming the scroll bar max & min values have been set in its properties, this might also do the job?
    Seems to cycle through the disallowed values fast enough to me.
    Code:
    vba Code 3 Private Sub ScrollBar1_Change()

    Cheers ��
    Goma (NASA)

  7. #7
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,408
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: 1, 2 skip a few 99, 100 scenarios

    Quote Originally Posted by GomaPile View Post
    Hello gentleman Mikerickson & Peter,

    As always, I appreciate your support and thank you gentlemen for the time to help me once again.
    You are very welcome. Thanks for your comments.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  8. #8
    Board Regular GomaPile's Avatar
    Join Date
    Jul 2006
    Posts
    296
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: 1, 2 skip a few 99, 100 scenarios

    ...with the help from this forum Peter my edit changes;

    Code:
    Private Sub ScrollBar1_Change()
      Static lastScrollValue As Long
      
      Application.ScreenUpdating = False
      With ScrollBar1
        Select Case .Value
          Case .Max
            .Value = .Min
          Case _
          13 To 23, _
          36 To 40, _
          42 To 52, _
          65 To 75, _
          86 To 96, _
          106 To 110, _
          119 To 129, _
          135 To 139, _
          142 To 146, _
          148 To 152, _
          154 To 164, _
          169 To 173, _
          175 To 179, _
          184 To 188, _
          195 To 195
            .Value = .Value + IIf(.Value > lastScrollValue, 1, -1)
        End Select
        lastScrollValue = .Value
      End With
      Application.ScreenUpdating = True
      
    End Sub
    Thanks

Some videos you may like

User Tag List

Tags for this Thread

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
  •