Do autonumber in forms

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I have this question. It is possible If I open a form for the first time, this form should start at say 001 and would increment by 1 ONLY when it is saved. So the second time I open the forms it should read 002 ONLY if it was saved, if not it should open up as 001.
I want this number assigned automatically, with no intervention of the user and then at the end this number save in a spreadsheet. Is that possible.

Thanks I would appreciate your help on this matter.


4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What software are you talking about. If you are talking about access that is how works.
Sorry if I did not say anything before, I'm working with excel. I know acccess do that, but I want to do that in excel.

I have a form where the user enter the information of insurance discrepancies. I want an autonumber to be created everytime the user save the form, but also he or she could be able to know the number I mean that the number coul be seen on the form but can not be changed by the user..

Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You have to do it with code. You would need to lock the cell
and have the value populated via macro
That's the problem I do not know how to do it, do you have an example of an existing excel file that I can use. I would appreciate that.

Clara.. Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Yeah I kind of suspected that after I posted the message, I noticed your name. I guess you were the one who was looking for changing the text of the button.

The answer to this one is simple and not so simple. For that one has to look at the form. Here is the basic idea

To lock the cell.
First select all the cell and right click and go to format
There is option for protection. Uncheck the box.
Now right click the cell where you want that value to be and again go to format and enable the lock.
Now the lock is not operational till the sheet is lock. You need to go o Review tab and there is a protect worksheet option.

Now this all prevents a user to type in the value there.


Now to auto increment the lock cell. For that you have to change the code for the button

Write a sub like this


Sub incrementValue()
Dim currentValue As Variant

    'Assuming that the locked value is on sheet1 and cell C1
    currentValue = Sheets("Sheet1").Cell(1, "C").Value
    
    ' if the cell is blank then, value is 0
    If currentValue = "" Then currentValue = 0
    
    ' a safety issue. In case the value in cell c1 was not numeric then we dont want to mess up
    If (IsNumeric(currentValue)) Then
        currentValue = currentValue + 1
    
        Sheets("Sheet1").Cell(1, "C").Value = Right("000" & currentValue, 3)
    
    End If

End If



Now in the code for button, add one more line

Call  incrementValue
Hi, The sub should be in the form, the sheet a module, sorry, as you might noticed, I'm a newbie....

Thanks
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It is code for the form.