falimorad
asked on
Excel autogenerate number when file opened
Hi Guy,
I have no experience with macro programming in excel, but I am trying to figure out, how can I have a excel file opened and it auto generate and increments a number everytime someone opens this file. it must be unique everytime. I am using excel 2010.
Thanks
I have no experience with macro programming in excel, but I am trying to figure out, how can I have a excel file opened and it auto generate and increments a number everytime someone opens this file. it must be unique everytime. I am using excel 2010.
Thanks
If it is not important that the number follows a sequence, so if the only important is to be unique everytime, you can use this formula on the ceil:
= Int(Rand()*2000000000)
Otherwise you should program a macro on the open event to take a numer on a cell (i.e. A1) increment it and save the number on the cell A1, but this is a little more complex.
= Int(Rand()*2000000000)
Otherwise you should program a macro on the open event to take a numer on a cell (i.e. A1) increment it and save the number on the cell A1, but this is a little more complex.
This is what I was suggesting. Sample file attached.
Sid
Code Used
Sid
Code Used
Dim Numb As Long
Private Sub Workbook_Open()
Numb = Sheets("Temp").Range("A1").Value + 1
msgbox Numb
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Temp").Range("A1").Value = Numb
End Sub
Sample.xls
You state an incrementing number so the following does just that when placed in the thisworkbook module. Basically it initialises a customdocumentproperty if needed and stores the number there. The script includes a msgbox for demo purposes and a save as soon as the file opens so the increment is recorded.
Both the auto save and the msgbox can be deleted when you are happy
The attached file has the code and has been initialised .. was set at 2 at closure of test
Chris
Both the auto save and the msgbox can be deleted when you are happy
The attached file has the code and has been initialised .. was set at 2 at closure of test
Chris
Private Sub Workbook_Open()
If Not propexists("Ticker") Then
Me.CustomDocumentProperties.Add Name:="Ticker", LinkToContent:=False, Type:=msoPropertyTypeNumber, Value:=0, LinkSource:=False
End If
Me.CustomDocumentProperties("Ticker") = Me.CustomDocumentProperties("Ticker") + 1
Me.Save
MsgBox Me.CustomDocumentProperties("Ticker")
End Sub
Function propexists(strPropname) As Boolean
Dim prop As Object
For Each prop In Me.CustomDocumentProperties
If LCase(prop.Name) = LCase(strPropname) Then
propexists = True
Exit For
End If
Next
End Function
Workbook1.xls
BTW, recall the earlier statement the code self initialises so you simply put the supplied code in the thisworkbook and it works from the time it is first saved and then re-opened.
Chris
Chris
ASKER
Thanks Chris, I have already a excel file, and there is a cell where it has invoice number, and this is where I want the number to be generated on, and is there a place where I can store this number?
Did you check my post?
Sid
Sid
ASKER
Yes I did, do you have more description, or a site I can see how that is done.
If you open the file that I attached then you can see the code. Or if you have a sample file then maybe I can give you an example?
Sid
Sid
ASKER
Sorry I was just reading your first post, let me look more into the second
ASKER
hey Siddharthrout, so would it be random and unique everytime. How do I implement that in to my worksheet.
Do you want the number to be random or increment it every time?
Sid
Sid
ASKER
Lets keep it incrementing for simplicity. Most important it has to be different everytime it is opened, and is there a way for the number to be stored
Then the file that I gave above does what you want :)
What is the initial number that you want the series to start with?
Sid
What is the initial number that you want the series to start with?
Sid
ASKER
It doesnt matter it can start with 1, but how come everytime I save it and reopen it, it gives the same number?
Are macros enabled?
Sid
Sid
The reason why I ask is because every time I open it, it gives me a new number, provided I save the file before closing...
Sid
Sid
ASKER
OK when I close and save, it increments. What do I replace msgbox Numb, in order for that to be displayed on a particular cell. Is there a way to increment even if the document is not saved, where it is just closed. I see how you have in the temp worksheet the number that is going to be used to increment, is there a possible way we can have another workbook, where it stored all the numbers that have been used. After it closes it increments the temp worksheet, and stored it in another worksheet, so we have a track of every number used?
Thanks for all your help, you are very helpful
Thanks for all your help, you are very helpful
What do I replace msgbox Numb, in order for that to be displayed on a particular cell
Say you want to display the number in cell A1 of sheet1 then Replace the line
msgbox Numb
with
Sheets("Sheet1").Range("A1
Is there a way to increment even if the document is not saved, where it is just closed.
Yes. Delete the code Workbook_BeforeClose and Use this code
Dim Numb As Long
Private Sub Workbook_Open()
Numb = Sheets("Temp").Range("A1").Value + 1
Sheets("Sheet1").Range("A1").Value = Numb
Sheets("Temp").Range("A1").Value = Numb
End Sub
I see how you have in the temp worksheet the number that is going to be used to increment, is there a possible way we can have another workbook, where it stored all the numbers that have been used. After it closes it increments the temp worksheet, and stored it in another worksheet, so we have a track of every number used?
Yes you can do that as well.
You will then have to use this code... Autonumber.xls is the other workbook where the numbers are stored.
Dim Numb As Long
Private Sub Workbook_Open()
Dim wb1 As Workbook, wb2 As Workbook
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("C:\Autonumber.xls")
Numb = wb2.Sheets("Temp").Range("A1").Value + 1
wb1.Sheets("Sheet1").Range("A1").Value = Numb
wb2.Sheets("Temp").Range("A1").Value = Numb
wb2.Close savechanges:=True
End Sub
Sid
I am going out for a couple of hours so I wont be able to reply to your queries now. But I will, once I get back :)
However, I think I have answered all your main queries :)
Take Care,
Sid
However, I think I have answered all your main queries :)
Take Care,
Sid
ASKER
Thanks Sid, I tried having an autonumber file, but it doesnt store all the numbers, it just stores the number in A1 cell and gets updated with the new value. I decided to go with
Dim Numb As Long
Private Sub Workbook_Open()
Numb = Sheets("Temp").Range("A1") .Value + 1
Sheets("Sheet1").Range("A1 ").Value = Numb
Sheets("Temp").Range("A1") .Value = Numb
End Sub
The problem is, It will only increment when I save, close and reopen. It wont increment if I just close and reopen.
So there isn't a way to store the numbers used on a separate file or tab so that the numbers will be stored in A1 then the second number would be A2, A3... and so on?
Dim Numb As Long
Private Sub Workbook_Open()
Numb = Sheets("Temp").Range("A1")
Sheets("Sheet1").Range("A1
Sheets("Temp").Range("A1")
End Sub
The problem is, It will only increment when I save, close and reopen. It wont increment if I just close and reopen.
So there isn't a way to store the numbers used on a separate file or tab so that the numbers will be stored in A1 then the second number would be A2, A3... and so on?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sid your the best, thanks
ASKER
Best solution
Sid