Auto increment a cell

[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

 

[ryguy7272]

I have never tried this myself, but I've read a few posts (here) that refer
other people to this:
http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

Regards,
Ryan---

 

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

 

[ryguy7272]

Take a look at this:
http://www.anthony-vba.kefra.com/vba/vbabasic1.htm#Creating_Your_First_Macro


Regards,
Ryan---

 

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

 

[phale]

No, probably not---1st time user ;-)

Did you intend to reply to ME?

 

[Peter T]

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.

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

 

[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

 

[Arod]

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

 

[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

 

[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

 

[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

 

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

 

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

 

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