Macro to select next name in dropdown list

[fordmudslinger]

Thanks for your help in advance! I have a dropdown list with my customers name in it for invoicing. I am looking to assign a macro to a button that, once clicked, will change to the next customer name in the list. Is this possible?

 

[Rick Rothstein]

[color=darkblue]Sub[/color] Button_Click()
    [color=darkblue]Dim[/color] v [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]With[/color] Sheets("Invoice").Range("C12")
        [color=darkblue]If[/color] .Value = "" [color=darkblue]Then[/color]
            .Value = Sheets("Customers").Range("A2").Value
        [color=darkblue]Else[/color]
            v = Application.Match(.Value, Sheets("Customers").Range("A2:A25"), 0)
            [color=darkblue]If[/color] IsNumeric(v) [color=darkblue]Then[/color]
                .Value = Sheets("Customers").Range("A2:A25").Cells(v + 1, 1).Value
            [color=darkblue]Else[/color]
                .Value = ""
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

You know how I like to condense things, right? :wink: This produces the same output as your code...

Sub Button_Click()
  Dim v As Variant, CustomerList As Variant
  With Sheets("Invoice").Range("C12")
    CustomerList = Join(Application.Transpose(Sheets("Customers").Range("A2:A25")), "|")
    CustomerList = "|" & CustomerList & "||" & CustomerList
    .Value = Split(Split(CustomerList, "|" & .Value & "|")(1), "|")(0)
  End With
End Sub

 

[Rick Rothstein]

You know how I like to condense things, right? :wink: This produces the same output as your code...

Sub Button_Click()
  Dim v As Variant, CustomerList As Variant
  With Sheets("Invoice").Range("C12")
    CustomerList = Join(Application.Transpose(Sheets("Customers").Range("A2:A25")), "|")
    CustomerList = "|" & CustomerList & "||" & CustomerList
    .Value = Split(Split(CustomerList, "|" & .Value & "|")(1), "|")(0)
  End With
End Sub

Even more compact...

Sub Button_Click()
  Dim CustomerList As Variant
  CustomerList = "||" & Join(Application.Transpose(Sheets("Customers").[A2:A25]), "|") & "||"
  With Sheets("Invoice").[C12]
    .Value = Split(Split(CustomerList, "|" & .Value & "|")(1), "|")(0)
  End With
End Sub

 

[Rick Rothstein]

Even more compact...

Sub Button_Click()
  Dim CustomerList As Variant
  CustomerList = "||" & Join(Application.Transpose(Sheets("Customers").[A2:A25]), "|") & "||"
  With Sheets("Invoice").[C12]
    .Value = Split(Split(CustomerList, "|" & .Value & "|")(1), "|")(0)
  End With
End Sub

This is probably as compact as I can make it... it's a one-liner (just remove the line continuation character and rejoin the two lines to make it look like one)...

Sub Button_Click()
  Sheets("Invoice").[C12] = Split(Split("||" & Join(Application.Transpose(Sheets( _
     "Customers").[A2:A25]), "|") & "||", "|" & Sheets("Invoice").[C12] & "|")(1), "|")(0)
End Sub