Scrollbar linked to a cell: how to step decimals.

[pietrafesa]

Hello guys.
I have an excel sheet where ther is a cell (D27) and a ActiveX Scrollbar conneted to. The name of scrollbar is Track_RCS.
I'm over an old problem: when I click on the scrollbar cursor, the value insiede cell changes. That is fine. Moreover, when I change the value in the cell, the scrollbar cursor moves consequently and correctly as per new value. That is fine.
The problem I'm not able to resolve is that I would have "0.05" steps in the cell value, and not the "mandatory" step of "1". In other words, I would have that when I click on the cursor, the cell value increase or decrase with steps by 0.05 (I have to click 20 times in the same direction to change value of 1).
May you please help me with the VBS code?
Thank you very much indeed.
Roberto.

 

[Artik]

You can do this by using an intermediate cell.
For example, in ScrollBar's properties, LinkedCell set to D28, LargeChange=500, SmallChange=5
The result is supposed to display in D27, so in this cell insert the formula:

=D28/100

Done.

Artik

 

[pietrafesa]

Dear Mr Artik, good morning. Thank you very much indeed for your answer. Is there any other trick to resolve the problem, without involve an intermediate cell? Or, if any, a more "evoluted" mode? Thank you very much indeed. (for example with a "full VBA" solution").

 

[Artik]

Let's assume that we want to get values between 0 and 30 in D27.
ScrollBar1 settings:
LargeChange=50
SmallChange=5
LinkedCell -remove the cell address, the field should be empty
Max=3000
Min=0
In a sheet module, e.g. Sheet1 (not in a standard module, e.g. Module1!) paste the following procedures.

Option Explicit

Private Sub ScrollBar1_Change()
    Application.EnableEvents = False
    Me.Range("D27").Value = Me.ScrollBar1.Value / 100
    Application.EnableEvents = True
End Sub

Private Sub ScrollBar1_Scroll()
    Me.ScrollBar1.Value = Application.MRound(Me.ScrollBar1.Value, 5)
    Call ScrollBar1_Change
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "D27" Then
        Me.ScrollBar1.Value = Target.Value * 100
    End If
End Sub

Done again.

Artik

 

[pietrafesa]

Thank you very much, Artik! It works fine!
I impolitely allow myself to take advantage of your kindness to ask you how I can repeat the same thing for 8 different cells. That is, for each cell, I would like a bar that changes its value but with different steps. For example, for cell 1 the step must be one unit for each click, for cell two the step must be 0.05 for each click, for cell 3 the step must be 0.25 for each click and so on.
I take this chance to wish you a happy new year, wherever you are.
Roberto.

 

[Artik]

So that you can fairly understand how to modify the code for multiple ScrollBars, below I show a simple solution (almost copy-paste) for 3 controls. It is assumed that ScrollBar1 is associated with D27, ScrollBar2 with D30 and ScrollBar3 with D33. For the next controls, you copy the ScrollBar1_Change and ScrollBar1_Scroll routines, correcting the references to the corresponding controls. For each subsequent cell, you also add another condition in the Worksheet_Change procedure BEFORE line Application.EnableEvents = True. There are other ways to write code for multiple controls, but understanding it may be a bit more difficult for you.
In all ScrollBars, set the Min, Max, LargeChange and SmallChange properties respectively. With the latter, when the step is supposed to be 0.05 enter a value of 5 in the field, for step 0.25 enter 25, for step 1 enter 100, etc.

Option Explicit

Private Sub ScrollBar1_Change()
    Call ScrollBarN_Change(Me.ScrollBar1, Me.Range("D27"))
End Sub

Private Sub ScrollBar1_Scroll()
    Me.ScrollBar1.Value = Application.MRound(Me.ScrollBar1.Value, 5)
    Call ScrollBar1_Change
End Sub


Private Sub ScrollBar2_Change()
    Call ScrollBarN_Change(Me.ScrollBar2, Me.Range("D30"))
End Sub

Private Sub ScrollBar2_Scroll()
    Me.ScrollBar2.Value = Application.MRound(Me.ScrollBar2.Value, 25)
    Call ScrollBar2_Change
End Sub


Private Sub ScrollBar3_Change()
    Call ScrollBarN_Change(Me.ScrollBar3, Me.Range("D33"))
End Sub

Private Sub ScrollBar3_Scroll()
    Call ScrollBarN_Scroll(Me.ScrollBar3)
    Call ScrollBar3_Change
End Sub


Private Sub ScrollBarN_Scroll(Ctl As MSForms.ScrollBar)
    Ctl.Value = Application.MRound(Ctl.Value, 100)
End Sub

Private Sub ScrollBarN_Change(Ctl As MSForms.ScrollBar, Rng As Range)
    Application.EnableEvents = False
    Rng.Value = Ctl.Value / 100
    Application.EnableEvents = True
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    If Target.Address(0, 0) = "D27" Then
        Target.Value = Application.MRound(Target.Value, 0.05) '0.05=5/100
        Me.ScrollBar1.Value = Target.Value * 100
    End If
    
    If Target.Address(0, 0) = "D30" Then
        Target.Value = Application.MRound(Target.Value, 0.25) '0.25=25/100
        Me.ScrollBar2.Value = Target.Value * 100
    End If
    
    If Target.Address(0, 0) = "D33" Then
        Target.Value = Application.MRound(Target.Value, 1) '1=100/100
        Me.ScrollBar3.Value = Target.Value * 100
    End If
    
    Application.EnableEvents = True
End Sub

Artik

 

[pietrafesa]

Dear Mr Artik, gentleman, thank you for your new code, but it does not work properly.
May I kindly ask you to write me the "more complicated" code? (you wrote "...There are other ways to write code for multiple controls, but understanding it may be a bit more difficult for you.".
I would use the lighter code, if any. There are no problem for me to understand. Thank you very much indeed.
Roberto.