ID number generation - with prefix

Hi everyone,

I am currently working on a personal project, creating a macro in excel to better manage a register of businesses.
It is incomplete at the moment.

Currently, I have a userform with a field licencetext where the user can enter a number which acts as an ID number (the prefix "FB" is added during transfer of data from the userform to the sheet.

However, if the field is left blank, a would like excel to generate the next unique three digit number available (and verify that is not a duplicate)

Any ideas on how to do this? I have had a look at some other threads on the net, but can't seem to find something relevant or easy to understand - I am new to VBA and entirely self-taught mainly through just google and looking at examples... yea I know, it's pretty bad form XD


Edit. Attachment included - please note the code and layout of userform are not complete

generate the next unique three digit number available

assume you have a list of sorts....so take the last number and plus 1?

Post an example file with desensitized data if possible.

Try this.

Please Login or Register  to view this content.

so you have FB123, FB456, FB431

what would you expect to see as the next number?
FB124? then 125 126 etc
FB432 then 433 434 etc
FB456 then 457 458
or something else entirely?

That's not much use, really. In what way did it not seem to work? Outcome? Error messages? What???

There was no error when running macro, but regardless no new number generated and input into the worksheet.

Oh yes, it works.
Run this in your example file.

Please Login or Register  to view this content.

Can attach a worksheet where this works? I cannot seem to work out where it is going wrong on mine, the Msgbox does not appear. Perhaps I am putting it in the wrong place?

Your wish is my command.

Glad to help and thanks for rep+.