Auto increment a cell

A

Arod

I have an invoice in excel 2007 template in which I want to increment the
invoice number everytime I save it. Anybody who know how to do this, is
greatly appreciated. I'm not familiar with Microsoft Visual Basic Editor,
but if I can get instructions on how to do this I can manage to do it. thanks
 
A

Arod

Thanks ryguy7272,

The things is that I'm not familiar with the codes on VBE so i don't know
what to put. Also, I want the invoice number to increment automatically when
I save the file.
 
J

Jeff Johnson

The things is that I'm not familiar with the codes on VBE so i don't know
what to put. Also, I want the invoice number to increment automatically
when
I save the file.
Click to expand...

With the workbook active, open the VB Editor (Alt+F11). You should see a
tree view with a node that says something like "VBAProject (<workbook
name>)". Find the ThisWorkbook node below it and double-click it. A code
editor will appear.

You should see two dropdowns above the code editor. Drop down the left one.
Click the Workbook item. Now, in the right dropdown, select BeforeSave.
Enter the following code between Private... and End Sub lines (on its own
line):

Worksheets("<name of sheet containing your number>").Range("<address of the
cell you want to update>").Value = Worksheets("<same worksheet
name>").Range("<same address>").Value + 1

Save the workbook.

Please note that if the workbook has not been saved before (or the user
chooses Save As), this value will get incremented EVEN IF the user cancels
the Save dialog.
 
P

phale

I tried following your directions and typed in the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheet("Receivingtemplate2.xlt").Range("G2").Value=Worksheets("Receiving
template.xlt").Range("G2
").Value + 1


Private Sub Workbook_Open()

End Sub

And I am getting a syntax error. Can you tell from this what I am doing
wrong?
Thanks!
 
J

Jeff Johnson

phale said:
I tried following your directions and typed in the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Worksheet("Receivingtemplate2.xlt").Range("G2").Value=Worksheets("Receiving
template.xlt").Range("G2
").Value + 1


Private Sub Workbook_Open()

End Sub

And I am getting a syntax error. Can you tell from this what I am doing
wrong?
Click to expand...

Is that your EXACT code (i.e., did you copy and paste)? Because if so, the
first "Worksheets" is missing the "s" at the end.

Also, you appear to be trying to use the name of the WORKBOOK (i.e., the
file name) as an argument to the Worksheets() function instead of the name
of the SHEET as I told you. The workbook itself will already be open; you
don't need to reference it.
 
P

Peter T

Jeff Johnson said:
Is that your EXACT code (i.e., did you copy and paste)? Because if so, the
first "Worksheets" is missing the "s" at the end.

Also, you appear to be trying to use the name of the WORKBOOK (i.e., the
file name) as an argument to the Worksheets() function instead of the name
of the SHEET as I told you. The workbook itself will already be open; you
don't need to reference it.
Click to expand...

Just to add -
In addition to the missing "s" after Worksheet* you (OP) have another typo
for the sheet name (strange to name a sheet like a workbook-template name)

"Receivingtemplate2.xlt" vs "Receivingtemplate.xlt"

Sort out you spellings, then try something like this

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
Cancel As Boolean)

With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With

End Sub

Obviously change "Sheet1" and "A1" as required.

Regards,
Peter T
 
P

phale

Yes I did copy and paste--and when I got the syntax error, I tried making
changes like taking the s off the worksheets, etc.
I know so little about this I just assumed the file name should be
referenced. Am in the process of reading the VBE help file and taking the
tutorial you recommended.

So. . .would this code be correct?

Thanks for your help!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
 
A

Arod

Jeff, I tried what you told me to do and it worked perfect. Thank you so
much for your help.
 
P

phale

Okay this is what I have based on the earlier advice:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Worksheets("Sheet1").Range("G2").Value =
Worksheets("Sheet1").Range("G2").Value + 1

End Sub

Which gives me a runtime error 9 that says subscript out of range

The advice in the last post:
..Value=.Value+1
 
P

Peter T

The only way I can recreate your error is if there is no sheet named
"Sheet1" in the workbook, ie tab name. That's probably the reason for your
error.

You would get a different error number if G2 contained text or if the sheet
is protected

Regards,
Peter T
 
G

Gord Dibben

This would be easier as you were shown earlier.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Sheet1").Range("G2")
.Value = .Value + 1
End With
End Sub

The error 9 probably comes from you not having a "Sheet1"


Gord Dibben MS Excel MVP
 
A

Andre Laurence

Hello,

I am sorry to ressurect an old issue, but I was wondering if someone could tell me if the following is possible?

I have used :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Sheet1").Range("G3")
..Value = .Value + 1
End With
End Sub

With great success and would like to thank everyone in this thread, but I would like to know if there is a way to do this if the cell value is not only a number (ie instead of just 12080250, IS12080250). The above code is great, but issues a Runtime error 13 if there are any letters in the cell value.

Thank you in advance.
 
J

Jim Cone

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Jim Cone - Portland, Oregon USA - December 2008
'Adds 1 to the right most number in the cell.
Dim N As Long
Dim strOldText As String

With Worksheets("Sheet1").Range("G3")
If Not Right$(.Value, 1) Like "#" Then
MsgBox "Bad Entry"
Cancel = True
Else
strOldText = " " & .Value
For N = Len(strOldText) - 1 To 1 Step -1
If Not Mid$(strOldText, N, 1) Like "#" Then
.Value = LTrim(Left$(strOldText, N)) & _
CDbl(Right$(strOldText, Len(strOldText) - N)) + 1
Exit For
End If
Next
End If
End With
'for testing
'Cancel = True
End Sub
--
Jim Cone
Portland, Oregon USA
(thanks in advance is no thanks)



<Andre Laurence>
wrote in message
Hello,
I am sorry to ressurect an old issue, but I was wondering if someone could tell me if the following is possible?
I have used :

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
With Worksheets("Sheet1").Range("G3")
..Value = .Value + 1
End With
End Sub

With great success and would like to thank everyone in this thread, but I would like to know if there is a way to do this if the
cell value is not only a number (ie instead of just 12080250, IS12080250). The above code is great, but issues a Runtime error 13 if
there are any letters in the cell value.
Thank you in advance.
 
R

Rick Rothstein

Here is another approach you can consider...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim Position As Long
On Error GoTo BadValue
With Worksheets("Sheet1").Range("G3")
Position = InStr(.Value, StrReverse(Val(StrReverse(.Value))))
.Value = Left(.Value, Position - 1) & Mid(.Value, Position) + 1
End With
Exit Sub
BadValue:
MsgBox "Bad Entry"
Cancel = True
End Sub

--
Rick (MVP - Excel)


in message
news:[email protected]...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Add incremental character to cell value at change in value 449becomes 449-A 510 becomes 510-B 2
Look Up Formula With Veried Search Range 2
Need VBA Macro to Loop through pages and print counter in footer of each page 9
Invoicing and customer database 0
Converting excel to PDF, single page converts to multiple HELP! 1
Increment invoice number 1
Button to duplicate current forms (for an invoice) 1
Auto Change Formulas - Please Help. 2

Top