Invoice numbering?

[ljubo_gr]

Please, dear Excel super master users.
I have three(1.2.3) sheets, columns A and B on all three. In column B should be invoice number generated inserting data in A column, like, when typing "Bill" in A1, that should result MRE-INV-01 in B1. BUT, when typing "Jelen" in A1 on Sheet2, should be MRE-INV-02. and so on.
Invoice numbering should be generated by vlookup, Sheet1 to vlookup on sheet2&3, Sheet2 to vlookup sheet1&3... Then, probably IF function,,,, i don't know

 

[AlphaFrog]

This code will automatically add a new invoice number in column B when you type something in column A on the three sheets.

• Right-click on a sheet tab and select View Code from the pop-up context menu
• Double-click on ThisWorkbook in the VBAProject panel usually in the upper left of the VBA Editor
• Paste the code from below in the VBA editor
• Change the names of the three sheets to your sheet names.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_SheetChange([COLOR=darkblue]ByVal[/COLOR] Sh [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR], [COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Sh.Name = "[COLOR=#ff0000]Sheet1[/COLOR]" [COLOR=darkblue]Or[/COLOR] Sh.Name = "[COLOR=#ff0000]Sheet2[/COLOR]" [COLOR=darkblue]Or[/COLOR] Sh.Name = "[COLOR=#ff0000]Sheet3[/COLOR]" [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Column = 1 [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] Target.Value <> "" [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]With[/COLOR] Target.Offset(, 1)
                        .Value = Application.Max(Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("B:B"), _
                                                 Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("B:B"), _
                                                 Sheets("[COLOR=#ff0000]Sheet3[/COLOR]").Range("B:B")) + 1
                        .NumberFormat = """MRE-INV-""00"
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    Target.Offset(, 1).Value = ""
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

 

[ljubo_gr]

Iesu Christi, how fast

So this code should give me unique invoice numbers from MRE-INV-00 on all three sheets? Should i lock columns B?
And, where is column A in code?

 

[jtakw]

This code will automatically add a new invoice number in column B when you type something in column A on the three sheets.

• Right-click on a sheet tab and select View Code from the pop-up context menu
• Double-click on ThisWorkbook in the VBAProject panel usually in the upper left of the VBA Editor
• Paste the code from below in the VBA editor
• Change the names of the three sheets to your sheet names.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_SheetChange([COLOR=darkblue]ByVal[/COLOR] Sh [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR], [COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Sh.Name = "[COLOR=#ff0000]Sheet1[/COLOR]" [COLOR=darkblue]Or[/COLOR] Sh.Name = "[COLOR=#ff0000]Sheet2[/COLOR]" [COLOR=darkblue]Or[/COLOR] Sh.Name = "[COLOR=#ff0000]Sheet3[/COLOR]" [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] [COLOR="#00FF00"]Target.Column = 1[/COLOR] [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] Target.Value <> "" [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]With[/COLOR] Target.Offset(, 1)
                        .Value = Application.Max(Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("B:B"), _
                                                 Sheets("[COLOR=#ff0000]Sheet2[/COLOR]").Range("B:B"), _
                                                 Sheets("[COLOR=#ff0000]Sheet3[/COLOR]").Range("B:B")) + 1
                        .NumberFormat = """MRE-INV-""00"
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]Else[/COLOR]
                    Target.Offset(, 1).Value = ""
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

Hi,

Above in Green, refers to Column A.

 

[ljubo_gr]

Not so good, but BIG THANK YOU!
Problem, someone could type in column B, lets say, number 3, which is already taken. I need error warning. Or lock column B. Also i need on sheet1 lets say, in cell C1, to display me last used invoice number. Thank you so much.

 

[ljubo_gr]

No. Don't lock column B. I need to be able to skip 10 or so numbers. Exp: 1, 2,3,4, and user type in column B number 14, then Excel starts new invoice number from 14. But, i still need error warning when entering dups OR numbers in between 4-14(skiped numbers).

 

[AlphaFrog]

Try this...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Workbook_SheetChange([COLOR=darkblue]ByVal[/COLOR] Sh [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Object[/COLOR], [COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Sh.Name = "Sheet1" [COLOR=darkblue]Or[/COLOR] Sh.Name = "Sheet2" [COLOR=darkblue]Or[/COLOR] Sh.Name = "Sheet3" [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]If[/COLOR] Target.Count = 1 [COLOR=darkblue]Then[/COLOR]
            [COLOR=darkblue]If[/COLOR] Target.Value <> "" [COLOR=darkblue]Then[/COLOR]
                [COLOR=darkblue]If[/COLOR] Target.Column = 1 [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]With[/COLOR] Target.Offset(, 1)
                        .Value = Application.Max(Sheets("Sheet1").Range("B:B"), _
                                                 Sheets("Sheet2").Range("B:B"), _
                                                 Sheets("Sheet3").Range("B:B")) + 1
                        .NumberFormat = """MRE-INV-""00"
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
                [COLOR=darkblue]ElseIf[/COLOR] Target.Column = 2 [COLOR=darkblue]Then[/COLOR]
                    [COLOR=darkblue]If[/COLOR] Application.CountIf(Sheets("Sheet1").Range("B:B"), Target) + _
                       Application.CountIf(Sheets("Sheet2").Range("B:B"), Target) + _
                       Application.CountIf(Sheets("Sheet3").Range("B:B"), Target) > 1 [COLOR=darkblue]Then[/COLOR]
                            MsgBox "Duplicate Invoice Number", vbExclamation, "Invalid Entry"
                            Target.ClearContents
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]

If you want to see the last Invoice Number in Sheet1 C1, put this formula in that cell.
=MAX(Sheet1!B:B,Sheet2!B:B,Sheet3!B:B)

 

[ljubo_gr]

And FINAL, one more: UNDO(ctrl+z) not working

I have this code, also, in this workbook, and ctrlZ working, but when i add your code, ctrlZ is gone

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If ActiveCell.Column() > 21 Or ActiveCell.Row() > 61 Or ActiveCell.Row() = 1 _
    Or ActiveCell.Value = "" Then
    GoTo ifERROR
    Else
    Application.ScreenUpdating = True
ifERROR:
    End If
End Sub

 

[ljubo_gr]

I made mistake, invoice numbers should start like: INV-1-16, INV-2-16....
ANd i really would like to have back ctrlZ. I cannot F8 code, why?

 

[AlphaFrog]

Only the UNDO for changes to column A are gone. Changes to other columns should still UNDO. I don't know how to fix that and still have automatic invoice numbers.