Scroll Data validation list with spin buttons

baunataler

New Member
Joined
Apr 11, 2018
Messages
6
Hello everyone,

I have an Excel sheet where I inserted in a cell (C1) a list of values - this created a drop down where I can select the value that I want, then the table below is updated accordingly.

I would like to add a couple of spin buttons to scroll between the values - one click instead of two. So, for SpinUp would advance to the next value and SpinDown to the previous.

I searched all over the place but could not find what I was looking for.

Is there a way to do this?
Any help is much appreciated.

Thanks in advance!

Larry
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Welcome to MrExcel.
Try this:-
Assuming your drop down list is in "C1" then try this in your SpinButton Change Event.
Code:
Private [COLOR=navy]Sub[/COLOR] SpinButton1_Change()
[COLOR=navy]Dim[/COLOR] rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] rng = Range(Range("C1").Validation.Formula1)
[COLOR=navy]If[/COLOR] SpinButton1.Value > 0 And SpinButton1.Value <= rng.Count [COLOR=navy]Then[/COLOR]
    Range("D1").Value = rng(SpinButton1.Value)
[COLOR=navy]End[/COLOR] If
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick
 
Last edited:
Upvote 0
Sorry !!
First, I altered the Cell address to suit your Thread requirements and only altered it in one place
NB:- Try this for Validation data on same sheet:-
Code:
Private Sub SpinButton1_Change()
Dim rng As Range
Set rng = Range(Range("C1").Validation.Formula1)
If SpinButton1.Value > 0 And SpinButton1.Value <= rng.Count Then
    Range("C1").Value = rng(SpinButton1.Value)
End If
End Sub

NB:- You could run the code successfully with out the need for the validation cell , as below
NB:- This is "Spinbutton2", and the results are in "F1"
NB:- Validation Data on same sheet
Code:
Private Sub SpinButton2_Change()
Dim rng As Range
Set rng = Range("A1:A12")
If SpinButton2.Value > 0 And SpinButton2.Value <= rng.Count Then
    Range("F1").Value = rng(SpinButton2.Value)
End If
End Sub

If your validation data is on another sheet, then you need to name that range in the "Names Box"
I have called it "Mydata" (see code below.)
Then using "SpinButton3" code, as shown below, with the results to show in "K1"
NB:- alter references to suit:-
Code:
Private Sub SpinButton3_Change()
Dim rng As Range
Set rng = ActiveWorkbook.Names("Mydata").RefersToRange
If SpinButton3.Value > 0 And SpinButton3.Value <= rng.Count Then
    Range("K1").Value = rng(SpinButton3.Value)
End If
End Sub


Regrds Mick
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,315
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top