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

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

--
RyGuy


"Arod" wrote:

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

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" wrote:

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

--
RyGuy


"Arod" wrote:

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

"Arod" wrote in message
...

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.

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.

Take a look at this:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro


Regards,
Ryan---

--
RyGuy


"Jeff Johnson" wrote:

"Arod" wrote in message
...

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.

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.

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").Va lue=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!



"Jeff Johnson" wrote:

"Arod" wrote in message
...

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.

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.

"ryguy7272" wrote in message
...

Take a look at this:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro

Did you intend to reply to ME?

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

"Jeff Johnson" wrote:

"ryguy7272" wrote in message
...

Take a look at this:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro

Did you intend to reply to ME?

"phale" wrote in message
...
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").Va lue=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?

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.

"Jeff Johnson" wrote in message
news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
"phale" wrote in message
...
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").Va lue=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?

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

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
Boolean)
Worksheets("Sheet1").Range("G2").Value=Worksheets( "Sheet1").Range("G2
").Value + 1


"Jeff Johnson" wrote:

"phale" wrote in message
...
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").Va lue=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?

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.

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

"Jeff Johnson" wrote:

"Arod" wrote in message
...

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.

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.

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" wrote:

"Jeff Johnson" wrote in message
news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
"phale" wrote in message
...
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").Va lue=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?

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

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

"phale" wrote in message
...
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" wrote:

"Jeff Johnson" wrote in message
news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
"phale" wrote in message
...
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").Va lue=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?

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

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

On Tue, 6 May 2008 11:56:01 -0700, phale
wrote:

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" wrote:

"Jeff Johnson" wrote in message
news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
"phale" wrote in message
...
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").Va lue=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?

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

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.

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.

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)


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

New my auto invoice number example.


Auto numbering with txt
Auto total
Price List
View here : http://netmerkez.wordpress.com/excel...oice-template/

Download he http://hotfile.com/dl/139670745/2ff1...umber.rar.html

On Wednesday, April 30, 2008 2:06 PM Aro wrote:

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


On Wednesday, April 30, 2008 2:31 PM ryguy727 wrote:

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

Regards,
Ryan---

--
RyGuy


"Arod" wrote:


On Wednesday, April 30, 2008 3:28 PM Aro wrote:

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" wrote:


On Wednesday, April 30, 2008 4:16 PM Jeff Johnson wrote:

"Arod" wrote in message
...


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.


On Thursday, May 01, 2008 11:51 AM ryguy727 wrote:

Take a look at this:
http://www.anthony-vba.kefra.com/vba...ur_First_Macro


Regards,
Ryan---

--
RyGuy


"Jeff Johnson" wrote:


On Thursday, May 01, 2008 1:36 PM phal wrote:

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").Va lue=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!



"Jeff Johnson" wrote:


On Thursday, May 01, 2008 1:48 PM Jeff Johnson wrote:

Did you intend to reply to ME?


On Thursday, May 01, 2008 1:56 PM phal wrote:

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

"Jeff Johnson" wrote:


On Friday, May 02, 2008 9:22 AM Jeff Johnson wrote:

"phale" wrote in message
...

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.


On Friday, May 02, 2008 9:47 AM Peter T wrote:

"Jeff Johnson" wrote in message
news:5Z6dnal8Z4UdiIbVnZ2dnUVZ_oKhnZ2d@datapex...
Worksheet("Receivingtemplate2.xlt").Range("G2").Va lue=Worksheets("Receiving

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


On Friday, May 02, 2008 9:50 AM phal wrote:

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

"Jeff Johnson" wrote:


On Monday, May 05, 2008 2:00 PM Aro wrote:

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

"Jeff Johnson" wrote:


On Tuesday, May 06, 2008 2:56 PM phal wrote:

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" wrote:


On Tuesday, May 06, 2008 3:12 PM Peter T wrote:

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

"phale" wrote in message
...
Boolean)
Worksheet("Receivingtemplate2.xlt").Range("G2").Va lue=Worksheets("Receiving
doing
the
the
name
you
typo
name)


On Tuesday, May 06, 2008 3:31 PM Gord Dibben wrote:

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

On Tue, 6 May 2008 11:56:01 -0700, phale
wrote:


On Friday, December 19, 2008 10:54 PM Andre Laurence wrote:

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.


On Wednesday, July 21, 2010 5:58 AM Kobus Strydom wrote:

Sorry



Here is an example of the code I am using.



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

Worksheets("Sheet1").Range("K10").Value = Worksheets("Sheet1").Range("K10").Value + 1

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)



Dim FName As String

Dim FPath As String



FPath = "C:\Reports"

FName = Sheets("Sheet1").Range("K10").Text

ThisWorkbook.SaveAs Filename:=FPath & "\Flameproof panel report" & FName

End Sub



Private Sub Workbook_Open()



End Sub


On Wednesday, July 21, 2010 6:03 AM Kobus Strydom wrote:

Sorry again



Looks like my first post didn't post.



I have used the code and it worked great.



What I now want to do is to give the user an option, like when the file is only opened for viewing.



I was wondering if on opening the document the user can get a question "Is this a new report?" with a Yes/No option. On Yes, run the macro, on No, stop the macro and just view the document.



Here is the code I used.



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

Worksheets("Sheet1").Range("K10").Value = Worksheets("Sheet1").Range("K10").Value + 1

End Sub



Private Sub Workbook_BeforeClose(Cancel As Boolean)



Dim FName As String

Dim FPath As String



FPath = "C:\Reports"

FName = Sheets("Sheet1").Range("K10").Text

ThisWorkbook.SaveAs Filename:=FPath & "\Flameproof panel report" & FName

End Sub



Private Sub Workbook_Open()



End Sub