Next / Previous Button in Dropdown Values (Name Range) in Excel

If you are searching for VBA code for next button then I am sure that you have used dropdown in Excel. To change the value in dropdown it becomes necessary to click on drop down and select any value. It becomes very time consuming process. Today I will show you how to change the value in drop down or any cell by next button or previous button in excel

I will guide you to create an excel sheet where country name will be changed in a dropdown by clicking on button.

Method

Step 1: Create a small list of countries and name the sheet ("List" in below example)

2. Create Name range for this list

Select list -> right click -> Define Name -> Entername and save

3. Now create a drop down and link it to the list of countries.

Data -> Data Validation -> Select List -> and select range A2:A12

4. Create two VBA modules and place following code

For Next Button

Sub NextButton_Click()
    
    Dim locationList As Range
    Set locationList = Worksheets("SheetName").Range("NameRange")
    
    Dim currentLocation As Range
    Set currentLocation = Worksheets("SheetName").Range("DROPDOWNCELL")
    
    For i = 1 To locationList.Rows.Count
        If currentLocation.Value = locationList.Cells(i, 1) Then
            If i = locationList.Rows.Count Then
                currentLocation.Value = locationList.Cells(1, 1)
            Else
                currentLocation.Value = locationList.Cells(i + 1, 1)
            End If
            i = locationList.Rows.Count
        End If
    Next i
    
End Sub

For Previous Button

Sub PrevButton_Click()
    
    Dim locationList As Range
    Set locationList = Worksheets("SheetName").Range("NameRange")
    
    Dim currentLocation As Range
    Set currentLocation = Worksheets("SheetName").Range("DROPDOWNCELL")
    
    For i = 1 To locationList.Rows.Count
        If currentLocation.Value = locationList.Cells(i, 1) Then
            If i = 1 Then
                currentLocation.Value = locationList.Cells(locationList.Rows.Count, 1)
            Else
                currentLocation.Value = locationList.Cells(i - 1, 1)
            End If
            i = locationList.Rows.Count
        End If
    Next i


End Sub

This code for our sheet will be

Sub NextButton_Click()
    
    Dim locationList As Range
    Set locationList = Worksheets("List").Range("CountryList")
    
    Dim currentLocation As Range
    Set currentLocation = Worksheets("List").Range("D3")
    
    For i = 1 To locationList.Rows.Count
        If currentLocation.Value = locationList.Cells(i, 1) Then
            If i = locationList.Rows.Count Then
                currentLocation.Value = locationList.Cells(1, 1)
            Else
                currentLocation.Value = locationList.Cells(i + 1, 1)
            End If
            i = locationList.Rows.Count
        End If
    Next i
    
End Sub

Sub PrevButton_Click()
    
    Dim locationList As Range
    Set locationList = Worksheets("List").Range("CountryList")
    
    Dim currentLocation As Range
    Set currentLocation = Worksheets("List").Range("D3")
    
    For i = 1 To locationList.Rows.Count
        If currentLocation.Value = locationList.Cells(i, 1) Then
            If i = 1 Then
                currentLocation.Value = locationList.Cells(locationList.Rows.Count, 1)
            Else
                currentLocation.Value = locationList.Cells(i - 1, 1)
            End If
            i = locationList.Rows.Count
        End If
    Next i


End Sub


Step  5: Now create next and previous button

Developer Tab -> Insert (Form Control) -> Create button

You can rename those button by right click -> Edit text

Step 6: Right click on button and click on Assign Macro and select respective macro

Step 8: Now repeat same process for previous button and assign macro "PrevButton_Click"

Step 9: Now click on next button you will get value "Pakistan" in D3. Similarly you can use this multiple times to navigate thru list at ease.



I hope this post was useful for you. If you really found this helpful then please share our blog with your friends thru emails or social sites. Keep Coming !!! Many new tricks to come.