can you set scrollbutton dynamically with vba?

[ticc888]

I know that we can use properties of the scroll button to set how much each click shoudl increment by, however, I'm looking for a solution where a user can be prompted to enter a vlaue of each increment, than this enforces into the scroll button.

because with my spreadsheet, it may not be suitable that each increment is increased by say dollar value of 10 000 represetning $10 000, (eg. the principal sum might be far too alrge like in teh billions, hence no point increasing by 10 000, as we'll be here all day if you like!).

is thi spossible and does anyone have an examp,.e?

cheers

 

[pbornemeier]

I am not sure if you are working with the scroll bar or the spin button, so I choose the spin button. This code will seet the max, min and change value for the spin button named SpinButton1 on Sheet1 when the command button:

Private Sub CommandButton1_Click()
    Sheet1.SpinButton1.SmallChange = 25
    Sheet1.SpinButton1.max = 1000
    Sheet1.SpinButton1.Min = -1000
End Sub

You may want to make the spin button step vary automatically, and give the user an option to override as follows. This code assumes the principal value is in A1. It sets the spin button stetp value to a 'nice' number about 1% of the value in A1:

Private Sub CommandButton1_Click()
    Dim varInput As Variant
    varInput = InputBox("Enter the change increment or press 'Enter' to accept this default", , 10 ^ (Int(Log(Range("A1").Value) / Log(9.99)) - 2))
    If Val(varInput) > 0 Then
        Sheet1.SpinButton1.SmallChange = CLng(varInput)
    Else
        Sheet1.SpinButton1.SmallChange = 10
    End If
    Sheet1.SpinButton1.max = Range("A1")
    Sheet1.SpinButton1.Min = 0
End Sub

 

[ticc888]

thanks pbornemeier,

that's perfect, i;ll give it a try, it looks like what i'm lookking for..

i'll give it a go.. cheers