Next and Back Options on Drop Down Boxes

[ianb]

Hi,


Here is the Next Button option for Drop Downs. Works fine :

[pre]

Sub DropDownUpdateNext()

Sheets("Dashboard").Select
Application.Goto Range("a1")
With Sheets("Dashboard")
.Select
With .DropDowns("Drop Down 1")
Do
If .ListIndex < .ListCount Then
.ListIndex = .ListIndex + 1
Else
.ListIndex = 1
End If
Debug.Print .ListIndex, .ListCount, .List(.ListIndex)
Loop While .List(.ListIndex) = ""
End With
End With

End Sub

[/pre]
If I change the line to :


.ListIndex = .ListIndex - 1


This works fine for a back button until I get to the start and then it errors.

What other changes are required for the Back button to work as for the Next button it goes from End to Start. Back button cannot goto from Start to End.


Thanks.

 

[NARAYANK991]

Hi Ian ,


I have not tried this out , but I think it should work :

[pre]

Sub DropDownUpdateBack()
Sheets("Dashboard").Select
Application.Goto Range("a1")
With Sheets("Dashboard")
With .DropDowns("Drop Down 1")
Do
If .ListIndex > 1 Then
.ListIndex = .ListIndex - 1
Else
.ListIndex = .ListCount
End If
Debug.Print .ListIndex, .ListCount, .List(.ListIndex)
Loop While .List(.ListIndex) = ""
End With
End With
End Sub

[/pre]
Additionally , I have removed the .Select statement , since it is redundant ; you are selecting the Dashboard tab in the very first statement.


Narayan

 

[ianb]

Mnay Thanks works perfectly. I can now do the NEXT and BACK Buttons for each Drop Down Box with Buttons in VBA. thanks.