automatically increment field by one
 

Hi all

I have an excel spreadsheet that contains a field that we at present
manually update and therefore have to keep checking the last sequence number
used.
I would like to somehow create a way to auto update the field so that when
opened the file will update the number (similar to purchase order
sequencing). The field will also need to be secure so that it cannot be over
written.

Any idea's ?

automatically increment field by one
 

You could do this with some code in the Workbook_Open event

For example

Private Sub Workbook_Open()
With Sheet1
.Unprotect Password:="ABCD"
.Range("A1").Value = .Range("A1").Value + 1
.Protect Password:="ABCD"
End With
End Sub

This will unlock a protected sheet with the codename of Sheet1 with a
password of ABCD then it increments the value of A1 on Sheet1 and locks it
down again. (You will need to unlock all cells to work with them when the
sheet is protected, leaving A1 the locked cell to protect from meddling
hands

If you need to know where the code goes see here and look for the
ThisWorkbook module.

http://www.nickhodge.co.uk/vba/vbaim....htm#eventcode

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
www.nickhodge.co.uk
HIS


"Dingbat" wrote in message
...
Hi all

I have an excel spreadsheet that contains a field that we at present
manually update and therefore have to keep checking the last sequence
number
used.
I would like to somehow create a way to auto update the field so that when
opened the file will update the number (similar to purchase order
sequencing). The field will also need to be secure so that it cannot be
over
written.

Any idea's ?

automatically increment field by one
 

Private Sub Workbook_Open()
With Worksheets("Sheet1").Range("A1")
.Value = .Value + 1
End With
End Sub

'This is workbook event code.
'To input this code, right click on the Excel icon on the worksheet
'(or next to the File menu if you maximise your workbooks),
'select View Code from the menu, and paste the code

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Dingbat" wrote in message
...
Hi all

I have an excel spreadsheet that contains a field that we at present
manually update and therefore have to keep checking the last sequence
number
used.
I would like to somehow create a way to auto update the field so that when
opened the file will update the number (similar to purchase order
sequencing). The field will also need to be secure so that it cannot be
over
written.

Any idea's ?