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?

 

[AlphaFrog]

Is it a Data Validation dropdown list?
What is the cell range that populates the list?

 

[fordmudslinger]

Is it a Data Validation dropdown list?
What is the cell range that populates the list?

Yes, it is a data validation list. The dropdown list is in C12 and the sheet name is Invoice. The data is in A2 thru A25 and that sheet name is Customers. They are both in the same workbook.

 

[AlphaFrog]

[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]

 

[fordmudslinger]

[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]

That worked perfectly. I assigned it to a button that has two other macros assigned to it and for some reason it does not work together; one macro is the one you just solved for me, the other is to change the invoice number to the next in sequence i.e: 1849 becomes 1850, and the last one prints, and saves it to a specified location. It will change the invoice number and save it, but when I assign the new macro to it, it only performs the new macro. Is there a way to assign all three macros to the one button? Thanks again for your help...I am just starting to use macros to make my invoicing go faster because my business is growing.

 

[AlphaFrog]

Show your code for the main macro and the other macros.

 

[fordmudslinger]

Show your code for the main macro and the other macros.

Sub nextinvoice()
Range("J13").Value = Range("J13").Value + 1
Range("a19:c45").ClearContents
End Sub
Sub SaveInvoiceWithNewName()
Dim NewFn As Variant
'copy invoice to a new workbook
ActiveSheet.Copy
NewFn = ("C:\Users\user\Dropbox\Lawns\Invoices\") & Range("c12").Value & " " & Range("J13").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFn, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
nextinvoice
End Sub

 

[AlphaFrog]

You can assign only one macro to a button. You could make one "Master" macro that calls three other macros and assign the Master to the button.

Sub Master()
    Call Button_Click
    Call nextinvoice
    Call SaveInvoiceWithNewName
End Sub

Please take note of my signature block below and use CODE tags when posting code.

 

[fordmudslinger]

You can assign only one macro to a button. You could make one "Master" macro that calls three other macros and assign the Master to the button.

Sub Master()
    Call Button_Click
    Call nextinvoice
    Call SaveInvoiceWithNewName
End Sub

Please take note of my signature block below and use CODE tags when posting code.

Awesome, the only problem was that it would skip over the next invoice number and return every other number. I looked at it and removed the "next invoice" in the other macro and it works pefectly. Thank you so much!ray:

 

[AlphaFrog]

You're welcome.