Dim mylist(), itemNo As Integer
Sub nextItem()
mylist = Array("Orange", "Apple", "Mango", "Pear", "Peach")
If Range("A2").validation.Value Then
For itemNo = 0 To 4
If ActiveSheet.Range("A2") = mylist(itemNo) And itemNo < 4 Then
Range("A2") = mylist(itemNo + 1)
Exit For
End If
Next
End If
End Sub
Sub previousItem()
mylist = Array("Orange", "Apple", "Mango", "Pear", "Peach")
If Range("A2").validation.Value Then
For itemNo = 0 To 4
If ActiveSheet.Range("A2") = mylist(itemNo) And itemNo > 0 Then
Range("A2") = mylist(itemNo - 1)
Exit For
End If
Next
End If
End Sub
The data validation is in a cell that is connected to the pivot table list which allow me to search for the item I need the data for and I use the the data validation to select the item if I filter for items so I can select it.Hi, below is the workaround. mylist contains the same items you have in data validation list.
Any reason of using Data Validation? You can add combo box in your sheet and can control effectively using VBA.
VBA Code:Dim mylist(), itemNo As Integer
VBA Code:Sub nextItem() mylist = Array("Orange", "Apple", "Mango", "Pear", "Peach") If Range("A2").validation.Value Then For itemNo = 0 To 4 If ActiveSheet.Range("A2") = mylist(itemNo) And itemNo < 4 Then Range("A2") = mylist(itemNo + 1) Exit For End If Next End If End Sub
VBA Code:Sub previousItem() mylist = Array("Orange", "Apple", "Mango", "Pear", "Peach") If Range("A2").validation.Value Then For itemNo = 0 To 4 If ActiveSheet.Range("A2") = mylist(itemNo) And itemNo > 0 Then Range("A2") = mylist(itemNo - 1) Exit For End If Next End If End Sub