Looking for a new formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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,

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)

Brian said:
I am looking for a formula that adds a sequential number to a file upon
Click to expand...
opening. I do not want this formula to do any protection. Any help would
be appreciated.
 
All I am looking for is a formula that does a sequential number in a certain cell upon opening of the file

Thanks

Brian
 
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
 
All I am looking for is a formula that does a sequential number in a certain
cell upon opening of the file.
Click to expand...

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

Brian said:
Have tried everything with this new formula. It appears to do nothing. I
Click to expand...
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.
 
Back
Top