CODEWITHSUNDEEP
vbaexcel
user3396581
user3396581

Reputation: 3

Automatically creating unique id

Im still very much a novice so be nice if I'm asking a silly question but this is my issue at the moment. I'm starting to create a database in excel for a large archive collection using a user form for the data inputting. The simple bit is that to help identify each item in the collection I want to give each item its own ID. The harder bit is that I want the ID to also include a prefix that makes it clear what sort of item it is. For example the follow prefixes;

Description  |  Prefix
-----------------------
Newsletter   |  NEW

Minutes      |  Min

Photograph   |  Pho

and to add to the challenge is to have the ID to be sequential under its own prefix (they by not be grouped together though) so the list of IDs could end up like this;

NEW1
NEW2
PHO1
PHO2
PHO3
MIN1
NEW3
MIN2

IF you can suggest anything to help me achieve this i would be most grateful. Thanks in advance.

Upvotes: 0

Views: 909

Answers (2)

Hutch
Hutch

Reputation: 301

In a database, it is really best to make your ID code be completely free of meaning. That is, do not include the "item type" as a part of your unique ID. It should be a field of its own. The ID code should be nothing more than a unique identifier.

Upvotes: 0

Doug Glancy
Doug Glancy

Reputation: 27478

This assumes a form with a Listbox that has the three choices, a Button to generate the next record ID and a Textbox to display it.

It assumes that the existing IDs are in Column A of the ActiveSheet when the form is activated:

Private Sub UserForm_Activate()
With Me.ListBox1
    .AddItem "PHO"
    .AddItem "NEW"
    .AddItem "MIN"
End With
End Sub

Private Sub CommandButton1_Click()
Dim ws As Excel.Worksheet
Dim Category As String

Set ws = ActiveSheet
Category = Me.ListBox1.Value
Me.TextBox1 = Me.ListBox1 & Application.Evaluate("=MAX(IFERROR(SUBSTITUTE('" & ws.Name & "'!A1:A10," & """" & Category & """" & ","""")*1,0))") + 1
End Sub

It uses Application.Evaluate, which evaluates an array formula as if it was entered into a cell. If the formula was entered into a cell and there were values in A1:A10, and you'd selected "NEW" in the listbox, it would look like this:

enter image description here

And here's the result in the form:

enter image description here

Upvotes: 2

Related Questions