Create Macro to automate a Idnumber

[Andy.Foster]

I am trying to create a macro that will auto generate a Id Number for items i wish to add into a userform type database. Firstly the id number has to be unique to the item prefeably id no 1 relating to the first itm entered etc. This Id number then placed in a textbox within a userform stopping user from changing (so that there are no duplicates). I also need it to alocate old Id numbers for items that have been deleted. The data is then put onto a spreadsheet on the first available row in spraedsheet (this also has to find any blank rows inbetween data that has been deleted due to the item being deleted. i would be grateful for any help.
Regards Af06ter

[xLJer]

How about this: use spreadsheet row numbers as a part or
the whole of the ID.

Assuming that the destination worksheet is only updated from the
Userform, and there is no sorting, deleting or inserting of rows,
you can maintain control of the ID numbers by using the first
available empty row number to generate the ID, and display it
in a label on the form.

This code shows how to generate the ID:

Please Login or Register  to view this content.

[Andy.Foster]

Thanks xLJer for code works great if if data already on spreadsheet. But come up with a runtime error if empty?

[xLJer]

Try this, which tests for an empty worksheet before
assigning a value to nextID.

Please Login or Register  to view this content.

You may need to change the worksheet reference where
I have used Activesheet, for example:

Please Login or Register  to view this content.

[Andy.Foster]

Cool it works except when u reopen userform I get error13 mismatch error any ideas?

[Kyle123]

At the risk of asking a daft question, if you need to create a table with an incrementing unique Primary Key, why are you using Excel rather than Access?

[Andy.Foster]

Hi Kyle, Ive just been appionted as Treasurer to a club. We have a Bar of which we need to stocktake once a Month, this was done on pen and paper by the previous treasurer as was the account. im now upgrading but need it all on same workbook if possible, and Im also trying to work it so that other users can do the job so trying to make it idiot proof hahha

[xLJer]

See if this version eliminates the error, which
deals with finding the first unused cell in column A.

Change the sheet name to the one you are using.

Please Login or Register  to view this content.

,
Note: the above code has been replaced.

[xLJer]

I replaced non-working code with the code you see in the previous post.

[Andy.Foster]

Hi XlJer Thanks for all your help on this, as you can see i am new to Vba Coding.. The problem I have now id there is a error message saying no range has been set? Can you help again.

[xLJer]

Can you post the code that you tried from the example I gave you.
Be sure to inclose the code with code tags, which can be done
using the pound-sign icon on the message toolbar.

I'll try to duplicate the error, however this may require someone
with your Excel version to resolve if I strike out.

Try my macro in the attached file and see if you get the same error.

[bheanloh]

Is it because this ->

Please Login or Register  to view this content.

should be this ->

Please Login or Register  to view this content.

[Andy.Foster]

xLjer Thanks your copy works a treat no errors. Thank you. Seems my problem has been solved

[xLJer]

Andy, you should include the corrections from forum member bheanloh,
where dots were added to the code. The dots cause the code to reference
the specific worksheet, which may not be the active worksheet.

I learned that when I ran the same code from a module and without the dots.
Even though I had "With..." and "End With" to reference a sheet, the code
acts on whatever sheet is active if you don't use dots. So please make the two corrections.

[Andy.Foster]

Thank you xLJer and bheanloh you have helped with my frustrations. Seems to work most of the time. Not sure if its excel itself but sometimes I have to Close down and reload as I occasionally get an error that if cant find any cells? Regards Andy

[Andy.Foster]

My Next problem, I cant find the Range Value to add the data to Spreadsheet?

[xLJer]

Code that works most of the time is not good enough.
See if this method of selecting the worksheet before searching
for the first empty cell in column A gives you 100% success.

Please Login or Register  to view this content.

[xLJer]

Do you mean that you don't know how to use the row number when it comes time to add the data to the blank row?

[Andy.Foster]

Solved Thanks xLjer for all your help. As for the Range Value, Im new to VBA but got there in the end.

Regards

Andy