1, 2 skip a few 99, 100 scenarios

[GomaPile]

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)

 

[mikerickson]

Something like this might work for you. Set the .Max of the scroll bar to 30

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

 

[Peter_SSs]

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.

 

[mikerickson]

Good catch

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

 

[Peter_SSs]

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.

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

 

[GomaPile]

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.

Something like this might work for you. Set the .Max of the scroll bar to 30

VBA Code 2 Private Sub ScrollBar1_Change()

Good catch

vba Code 2 Private Sub ScrollBar1_Change()

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.

vba Code 3 Private Sub ScrollBar1_Change()

Cheers ��
Goma (NASA)

 

[Peter_SSs]

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.

 

[GomaPile]

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

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