Looking for a new formula

[Brian]

I am looking for a formula that adds a sequential number to a file upon opening. I do not want this formula to do any protection. Any help would be appreciated.

 

[Bob Phillips]

Brian,

A re-post of a previous reply.

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)

I am looking for a formula that adds a sequential number to a file upon

opening. I do not want this formula to do any protection. Any help would
be appreciated.

 

[brian]

All I am looking for is a formula that does a sequential number in a certain cell upon opening of the file

Thanks

Brian

 

[Frank Kabel]

Hi Brian,
unfortunately this cannot be achieved without some VBA code. So one way
would be to paste Bob's code into your workbook module and use the
formula
=__REFNUM__ (or whatever you choose as name)
as formula in your target cell

Frank

 

[Harlan Grove]

All I am looking for is a formula that does a sequential number in a certain
cell upon opening of the file.

Understood!

Can't be done with just a formula. Requires a macro of some sort.

If you need this, you need to use a macro. If you can't use macros, you can't
have this functionality.

 

[Brian]

Have tried everything with this new formula. It appears to do nothing. I am using a blank sheet and there is no indication that this formula is working in one form or another. I have pasted the formula in the macro almost as is. I did move the 1 to the + sign other than that nothing was changed with pasting. Any help would be appreciated. Wish i could get this to work.

 

[Frank Kabel]

Hi Brian
where did you put this code?. you have to paste it in the workbook
module of your workbook:
- Open your workbook
- righ-click on the Excel symbol left to the menu entry 'File'^
- choose 'Code'
- In the VBA Editor paste the code
- close the VBA editor
- Enter the =__REF__ formula into your workbook, save your workbook and
close it.
- Open yor workbook

HTH
Frank

Brian wrote:ur

 

[Bob Phillips]

Brian,

Try posting me your workbook, I'll get it working for you.

--

HTH

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

Have tried everything with this new formula. It appears to do nothing. I

am using a blank sheet and there is no indication that this formula is
working in one form or another. I have pasted the formula in the macro
almost as is. I did move the 1 to the + sign other than that nothing was
changed with pasting. Any help would be appreciated. Wish i could get this
to work.