question

SanjayJadiya-5501 avatar image
0 Votes"
SanjayJadiya-5501 asked emilyhua-msft edited

I just want to get invoice no. to be incremented by 1

I just want to get invoice no. to be incremented by 1, I am using below VBA code to perform this

Private Sub Workbook_Open()
Range("D3").Value = Range("D3").Value + 1
End Sub


But here the problem is whenever i opened this excel file to see some information, the invoice no. get incremented,

Please suggest me some code so that whenever i copied the workbook and rename, Only then the invoice no. will increment by 1.


office-vba-dev
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@SanjayJadiya-5501
As your issue is related to VBA code, I would modify this tag to be “office-vba-dev”, thanks for your understanding.


0 Votes 0 ·

1 Answer

NothingLeftToLose-5122 avatar image
0 Votes"
NothingLeftToLose-5122 answered NothingLeftToLose-5122 edited

Re: get new invoice number

A different approach...
(you must press the shift key when clicking the button)
Consider hiding columns 1 and 2

103212-image.png

Code follows.
'---
Sub AssignInvoiceNumbers()
On Error GoTo Err_Handler
Dim lngNum As Long
Dim lngLast As Long
Dim strValueOne As String
Dim strValueTwo As String

If GetKeyState(vbKeyShift) < 0 Then
TryAgain:
strValueOne = ThisWorkbook.Worksheets("Sheet1").Range("A3").Value + 1
strValueTwo = VBA.InputBox("The next record number is: " &
ThisWorkbook.Worksheets("Sheet1").Range("A3").Value + 1 & vbCr &

"Enter the new number in the box below." &
" To make no change leave the box blank or press Cancel.",

"New Invoice Number")
If VBA.Len(strValueTwo) = 0 Then
Exit Sub
ElseIf VBA.Val(strValueOne) = VBA.Val(strValueTwo) Then
With ThisWorkbook.Worksheets("Sheet1")
.Range("A3").Value = VBA.Val(strValueTwo)
.Range("D3").Value = VBA.Val(strValueTwo)
lngLast = .Cells(.Rows.Count, 2).End(xlUp).Row + 1
.Cells(lngLast, 2).Value = Date & " to " & strValueTwo
End With
Exit Sub
Else
GoTo TryAgain
End If
End If
Exit Sub
Err_Handler:
Beep
Beep
End Sub
'---

NLtL
https://1drv.ms/u/s!Au8Lyt79SOuhiXoNjAh-_-zLi49O
(free excel programs)


image.png (20.8 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I omitted the code that goes at the very top of the module...
Add this code...

Option Explicit
If VBA7 Then
Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Else
Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
End If
'---

A number sign should precede the If, Else, EndIf lines.

'---
NLtL


0 Votes 0 ·

Not helpful in my scenario, as i want to increment in invoice no. when either the workbook copied or renamed,

We can develop a function which depend up on file name and then if the file name changed the function will run and it will automatically increment the invoice no.

0 Votes 0 ·

The list of 'events' that are available, for a workbook, do not include a workbook rename or workbook copy event.

103511-image.png

I doubt if any custom vba code created would be reliable enough.
You many want to consider revising your invoice system.



'---
NLtL

0 Votes 0 ·
image.png (15.3 KiB)