1, 2 skip a few 99, 100 scenarios

GomaPile

Active Member
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

MrExcel MVP
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
 

Peter_SSs

MrExcel MVP, Moderator
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

MrExcel MVP
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:

Peter_SSs

MrExcel MVP, Moderator
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:

GomaPile

Active Member
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
Code:
VBA Code 2 Private Sub ScrollBar1_Change()

Good catch
Code:
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.
Code:
vba Code 3 Private Sub ScrollBar1_Change()

Cheers &#55357;&#56842;
Goma (NASA)
 

Peter_SSs

MrExcel MVP, Moderator
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

Active Member
...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

This Week's Hot Topics

  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • find many based on a certain criteria
    good evening, I hope someone can help me? I have a workbook sheet 2 contains lots of data.... I would like to be able to find anything on sheet...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
  • Text Format
    I have a sheet for user to keyin the data. The format of the data can be 451 / 1903, 0012 / 9908 or 00287 / 0099. The number after the "/" is...
  • Macro to copy values across rows and transposing them and add the user id
    [FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Hi,[/COLOR][/SIZE][/FONT] [FONT=Times New...
Top