Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 ? |
#2
|
|||
|
|||
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 ? |
#3
|
|||
|
|||
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 ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fill the matched field automatically | Excel Discussion (Misc queries) | |||
How do I automatically increment worksheet number when pasting a . | Excel Worksheet Functions | |||
Field whose value increments automatically | Excel Discussion (Misc queries) | |||
Method to increment value in data field? | Excel Discussion (Misc queries) | |||
Method to increment value in data field? | Excel Discussion (Misc queries) |