invoice number based on date and with caracters

[haroune]

Hello Guys ,

i have this VBA code that generates auto invoice number , i could not change the code so that it increment the left 3 numbers by 1 , and check if month and year are correct

my invoice number is : 001/mm/yyyy , 001 is the reference of the invoice

Sub NextInvoice()
OldInvoice = Range("G2").Value
LeftDate = Left(OldInvoice, 5)
CurrDate = UCase(Format(Date, "mm/YY"))

If LeftDate = CurrDate Then
Range("G2").Value = CurrDate & _
Format(Right(OldInvoice, 3) + 1, "000")

Else
Range("G2").Value = CurrDate & "001"
End If

Range("C16:G55").ClearContents


End Sub



thanks team

 

[haroune]

hi guys , this is the best i could come up with but it does not work



Sub NextInvoice()
OldInvoice = Range("G2").Value
LeftDate = Right(OldInvoice, 7)
CurrDate = UCase(Format(Date, "mm/yyyy"))

If LeftDate = CurrDate Then
Range("G2").Value = (Format(Right(OldInvoice, 3) + 1, "000")) & "/" & CurrDate


Else
Range("G2").Value = "001" & "/" & CurrDate
End If

Range("C16:G55").ClearContents


End Sub

 

[AlphaFrog]

Sub NextInvoice()
    
    OldInvoice = Range("G2").Value
    RightDate = Right(OldInvoice, 7)
    CurrDate = UCase(Format(Date, "mm/yyyy"))
    
    If RightDate = CurrDate Then
        Range("G2").Value = Format(CInt(Left(OldInvoice, 3)) + 1, "000/") & CurrDate
    Else
        Range("G2").Value = "001/" & CurrDate
    End If
    
    Range("C16:G55").ClearContents
    
End Sub

 

[Osvaldo Palmeiro]

Another approach:

Sub NextInvoice()
 [G2] = Format(Left([G2], 3) + 1, "000/") & Format(Date, "mm/yyyy")
 Range("C16:G55").ClearContents
End Sub

 

[haroune]

thank you very much sir , it does work

but , i have another problem , is that the cell G2 does not contain only the invoice number ! it contains also the word " Ref : " , ie : G2 cell value = Ref : 001/06/2021 , and Ref : is in bold

really appreciate your input

 

[Osvaldo Palmeiro]

Try:

Sub NextInvoice()
 With [G2]
  .Value = "Ref : " & Format(Mid(.Value, 7, 3) + 1, "000/") & Format(Date, "mm/yyyy")
  .Font.FontStyle = "Regular"
  .Characters(1, 5).Font.Bold = True
 End With
 Range("C16:G55").ClearContents
End Sub

 

[haroune]

thank you sir ,
much appreciate your help

one last thing to ask you
in data validation i have a dynamic range i am using indirect formula by defining name to range this method is not picking up the formula how can i make this dynamic so i can update my list easily ( ie : i have list 1 of Vendors , and list 2 for their respective contacts dependent on the 1st list ) so in order to add a feature of possibility to type a new Vendor or an updated contact , i am using this code :
liste 1 : from data validation = Vendors
list 2 : from data validation : =INDIRECT(select the cell of Vendors)

and i want to know why this code is not working on the dependent list ( it works fine on all dropdown lists expect for those who have INDIRECT formula ) and does it work on merged cells ?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim xCombox As OLEObject
Dim xStr As String
Dim xWs As Worksheet
Dim xArr

Set xWs = Application.ActiveSheet
On Error Resume Next
Set xCombox = xWs.OLEObjects("DropListTemp")
With xCombox
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
If Target.Validation.Type = 3 Then
Target.Validation.InCellDropdown = False
Cancel = True
xStr = Target.Validation.Formula1
xStr = Right(xStr, Len(xStr) - 1)
If xStr = "" Then Exit Sub
With xCombox
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = xStr
If .ListFillRange = "" Then
xArr = Split(xStr, ",")
Me.DropListTemp.List = xArr
End If
.LinkedCell = Target.Address
End With
xCombox.Activate
Me.DropListTemp.DropDown
End If
End Sub
Private Sub DropListTemp_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case 9
Application.ActiveCell.Offset(0, 1).Activate
Case 13
Application.ActiveCell.Offset(1, 0).Activate
End Select
End Sub