Excel Number Formulae

[Huggies]

Excel Formulae needed

Can any one help me find a formulae please
I have a form created in Excel that uses consecutive numbers. is ther
a formulae I can use that will automatically change to the next numbe
on opening etc. I.E first form 1712 next 1713 and so on.

I have the formulae for the date which works fine and that saves m
having to enter todays date over and over again so a number formula
would make the form even easier.

Many thank

 

[Bob Phillips]

Huggies,

You could create a workbook name that is incremented on each open. This code
will do this for a name __RefNum__

Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__RefNum__")) Then
Me.Names.Add Name:="__RefNum__", RefersTo:=1
Else
Me.Names.Add Name:="__RefNum__", RefersTo:=Evaluate("__RefNum__") +
1
End If

CleanUp:
Application.EnableEvents = True
End Sub


You would use it in a worksheet like so

=__RefNum__

Just call it whatever suits.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Huggies]

Thanks for your help Bob

Afraid I need something a bit more simplified I can get around Excel
but Im afraid I'm no expert - need things explained a bit clearer

Sorry - thanks for your help any way

 

[Bob Phillips]

OK. Follow these instructions for your target workbook and then copy the
previously supplied code in

Go to the VB IDE (Alt-F11)
Click on the workbook name in the project explorer window (top left of
window)
Click Insert>Module
Paste the code in the code module that opens up
In the project explorer, ensure that the 'Microsoft Excel Objects' section
is expanded (the + sign beside it changes to a -)
Double-click the ThisWorkbook entry
Add this code to that code module

Use it by just adding =__RefNum__ in the appropriate cell.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Sorry, there was a bit too muchj in previous post. It should have been

OK. Follow these instructions for your target workbook and then copy the
previously supplied code in

Go to the VB IDE (Alt-F11)
Click on the workbook name in the project explorer window (top left of
window)
In the project explorer, ensure that the 'Microsoft Excel Objects' section
is expanded (the + sign beside it changes to a -)
Double-click the ThisWorkbook entry
Add this code to that code module

Use it by just adding =__RefNum__ in the appropriate cell.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

OK. Follow these instructions for your target workbook and then copy the
previously supplied code in

Go to the VB IDE (Alt-F11)
Click on the workbook name in the project explorer window (top left of
window)
Click Insert>Module
Paste the code in the code module that opens up
In the project explorer, ensure that the 'Microsoft Excel Objects' section
is expanded (the + sign beside it changes to a -)
Double-click the ThisWorkbook entry
Add this code to that code module

Use it by just adding =__RefNum__ in the appropriate cell.
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Huggies]

Hi Bob

I have pasted the code into the two windows but the line
in red is not accepted - Syntax error - and the first row in th
workbook window is highlighted yellow??

Private Sub Workbook_Open()
On Error GoTo CleanUp
Application.EnableEvents = False

If IsError(Evaluate("__RefNum__")) Then
Me.Names.Add Name:="__RefNum__", RefersTo:=1
Else

Me.Names.Add Name:="__RefNum__", RefersTo:=Evaluate("__RefNum__")+
1

End If

CleanUp:
Application.EnableEvents = True

Thanks Caro

 

[Bob Phillips]

Carol,

In red would suggest that we have wrap-around of a single line, and it has
gone to VBA as 2 lines. It looks like this is the culprit

Me.Names.Add Name:="__RefNum__", RefersTo:=Evaluate("__RefNum__")+
1

This should all be on one line

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)