Generating serial number on a textbox in a userform [SOLVED]

[nanobuns]

Hi,


I am encountering a problem in generating a serial number. Everytime i open the form the serial number always starts with "1" and on the second time i open the same form the serial number is generating correctly. I couldnt figure out what was wrong


I have this code on userform_initialize

[pre]

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("ChangeControlRegister")

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

With txtchange
.Value = Format(Val(Cells(Rows.Count, 1).End(xlUp)) + 1, "000")
.Enabled = False
End With
End sub
commandbutton1

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Call UserForm_Initialize
end sub

[/pre]
Thank you so much,

 

[nanobuns]

Here a link of sample file http://rapidshare.com/files/2304381574/CHANGE%20CONTROL%20REGISTER.xlsm


Thanks,

 

[SirJB7]

Hi, nanobuns!

VBA project is password protected and at open time an user Id and a password are required.

Regards!

 

[nanobuns]

Hi SirJB7,


Sorry, password is crpt123 for both.


Thanks,

 

[Smallman]

Hi Nanobuns


Your vb bugs out on the following line When you click on the Save(form) button;


iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Now the above line is all good but the two variables you use iRow and ws are commented out above this line and that sort of gear is not going to fly.


The first serial number as you put it which is called a change number in your form was 119. When you uncomment out the above two variables and put some data in the form and run it a second time the next time you click on the Add New button the Change Number is 120. This looks all good.


On a side note you have your code split into 9 separate modules. Some of the code is very small (a few lines) and takes up a whole module and most of it looks recorded. Your project could be significantly streamlined from just looking at it and you might do well to consolidate much of your code onto two or three modules for ease of management.


Anyways I hope that helps.


Take care


Smallman

 

[SirJB7]

Hi, nanobuns!

As well as for Smallman the user form opened when clicking on Add New button works for me, it displays the number 119 which it appears to be correct since your last one is 118.

If it doesn't work for you, please describe in detail every step you do after opening the file (clicks, inputs, all).

Regards!

 

[nanobuns]

Hi SirJB7 and smallman,


After logging in the worksheet "changecontrolregister" will appear then i have to click the add new button to load the userform.

It works for me but the thing is, when i click the add new button from the worksheet the number that generates from the change.no textbox is "1" and when i exit from the form and click the add new button again it generates 119.


I cant figure out what seems to be the problem


Thanks,

 

[SirJB7]

Hi, nanobuns!

I've yet done and now repeated exactly your described procedure and I get a 119 and not a 1 the 1st time. You should try downloading your uploaded file to a temp location and performing the test from there. Maybe there's something wrong with your actual file but not with the uploaded one.

Regards!

 

[nanobuns]

Hi SirJB7,


Now i know what was wrong i made the worksheet "changecontrolregister" hidden upon opening the workbook. Its working now, thank you so much for your time


have a great day,

 

[SirJB7]

Hi, nanobuns!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!