VBA Code for ID Number Increment on User Form in Text Box and Locked

[CGoley]

Hi All,

I've been working on a User Form to track inventory and other items. One entry has me stumped and I cannot get it to function. The first operation is to have a Unique ID that will increase by 1 when the UserForm is opened. I'm using the following code, but cannot get it to work.

There is a TextBox that's named ID with the following code associated with that TextBox.

''This increases the ID in the form.
'
Private Sub ID_Change()
    Range("ID").Value = Range("ID").Value + 1
End Sub

The code used for the Save button, which saves the form.

Private Sub cmbSaveInv_Click()    Dim lRow As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Asset List")
    lRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        With ws
        .Cells(lRow, 1).Value = Me.ID.Value
        .Cells(lRow, 2).Value = Me.cboxCategory.Value
        .Cells(lRow, 3).Value = Me.Manufacturer.Value
        .Cells(lRow, 4).Value = Me.cboxIvnType.Value
        .Cells(lRow, 5).Value = Me.RRAbv.Value
        .Cells(lRow, 6).Value = Me.RR.Value
        .Cells(lRow, 7).Value = Me.RNumber.Value
        .Cells(lRow, 8).Value = Me.Description.Value
        .Cells(lRow, 9).Value = Me.cboxCondition.Value
        .Cells(lRow, 10).Value = Me.AcquiredDate.Value
        .Cells(lRow, 11).Value = Me.MfgDate.Value
        .Cells(lRow, 12).Value = Me.RDate.Value
        .Cells(lRow, 13).Value = Me.PurchasePrice.Value
        .Cells(lRow, 14).Value = Me.MSRP.Value
        .Cells(lRow, 15).Value = Me.CurrentValue.Value
        .Cells(lRow, 16).Value = Me.cboxLocation.Value
        .Cells(lRow, 17).Value = Me.InventoryNotes.Value
    End With

I'll need help to correct this code to have it increase automatically.

The second part, I would like for this unique ID to auto populate the ID textbox and be locked to the end user so that they cannot alter that number, but have it save to the file once the save button is clicked.

Any help would be greatly appreciated.

Craig

 

[HalfAce]

Not sure what exactly you mean by 'adding' the images, but that sounds like a great subject for a new thread.

A good place to start checking that out might be John Walkenbach's site here.
Spreadsheet Page Excel Tips: Pasting An Image To A UserForm Control

Hope it helps.

 

[guest 73]

Well, to have the userform load up with a textbox named ID showing the value of the last used cell in column A (+1) you can use this in your userform_activate code:

 Private Sub UserForm_Activate()


'''This declares the data type of the variable "LstRw'
Dim LstRw As Long


'''This defines what the variable "LstRw' is to refer to. _
   (The row number of the last used cell in column A.)
LstRw = Cells(Rows.Count, "A").End(xlUp).Row


'''This tells the textbox named ID to equal the value of the last used cell in Col.A after adding 1 to it.
Me.ID.Value = Cells(LstRw, "A").Value + 1


End Sub

When you say you want this value to be non-editing, I assume you mean you don't want the user to be able to edit the textbox value in the userform. (yes?) If so, then in the properties of your textbox, look for and set the 'Locked' property to true.

I'm afraid I don't quite follow your last post, but does any of this help?

Hi there, could you use this code on a command button? I would like to keep the userform open for multiple entries, the "save" button transfer the information in database and clear the user form and the "new" button add a new ID?
Thank you