How to select next item in Data Validation list using VBA

Question

I have a Data Validation list with dates in it. When you change the date it effects what data is shown in the rest of the worksheet.

I would like to to create 2 command buttons,

"Next" - when clicked will move to the next date in the list, when it reaches the end of the list it goes back to the beginning of the list
"Previous" - when clicked will move to the previous date in the list, when it reaches the beginning of the list it will go to the end of the list

Is this possible?

I did look at List Box and Combo Box but got highly confused with the coding!!! Any help would be great!

score 2 · Answer 1 · answered Jun 03 '13 at 09:06

You have to distinguish 3 cases:

data validation with in-line list elements (e.g. Source = "1;2;3;4;5")
data validation with list elements in a range
List/Combo box

Case 1 is maybe the most difficult, because you can access the list elements only in a string and have to split them up into an array, get the index of the current selection and move the index with two buttones in order to get the wanted result

Case 2 is a bit simpler, but again you need to somehow keep track of the current position within the range defining your dates

Case 3 is maybe the most easiest to implement ... but still requires a certain coding effort, like

load list of dates into the list/combo box before first display (OnLoad or OnActivate
create code for the UP and DOWN buttons to increment/decrement the list box index, with automatic wrap-around

I suggest a 4th case to you ... use an ActiveX Spin Button ... this provides up and down functions in one element:

create your list of dates in a vertical named range DateList
reserve one more cell for the index of the Spin Button and name it DateIndex
using Developer ribbon, insert an ActiveX Spin Button (default name is SpinButton1)
set the LinkedCell property in SpinButton1 (be sure to be in Developer / Design mode; right click the Spin button and select "properties") to DateIndex
create the following code (still in design mode, right click the SpinButton and select "view code")

code

Private Sub SpinButton1_SpinDown()
    If SpinButton1 = 0 Then
        SpinButton1 = Range("DateList").Rows.Count
    End If
End Sub

Private Sub SpinButton1_SpinUp()
    If SpinButton1 = Range("DateList").Rows.Count + 1 Then
        SpinButton1 = 1
    End If
End Sub

At the cell where you want to display the selected date, enter formula =INDEX(DateList,DateIndex)
since you are working with named ranges, DateList and DateIndex may be in a different sheet (even a hidden one) than the user sheet.
if you want to create a copy of the currently chosen date at the cell where the user has currently put the cursor, add the following statement to the end of the SpinDown/Up Sub's (after End If: Selection = Range("DateList").Cells(SpinButton1, 1)

How to select next item in Data Validation list using VBA

1 Answers1