Scroll Data validation list with spin buttons

[baunataler]

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

 

[MickG]

Welcome to MrExcel.
Try this:-
Assuming your drop down list is in "C1" then try this in your SpinButton Change Event.

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

 

[baunataler]

Welcome to MrExcel.
Try this:-

Hi Mick,

doesn't work. When I type ?Range(Range("C1").Validation.Formula1) it returns an Error type 13 - Type mismatch

 

[MickG]

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

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

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

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

 

[baunataler]

Hi Mick,

thanks, it worked!

 

[MickG]

You're welcome