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]

Hi. As I understand it, you have a userform with a textbox named 'ID'. And you want to have the number value in the textbox to increment up by one with the userform activation. (Yes?)
If so, it looks to me like you have your code in the wrong place. What you have there will only execute when the value in your textbox gets changed, not on the userform being activated.

Where does the textbox get the value that's being added to when you call up the userform?
Where ever that is, if you use something like this behind the userform, (not the textbox), it should increment the number up each time the userform gets activated.

Private Sub UserForm_Activate()
Me.ID.Value = Me.ID.Value + 1
End Sub

Does that help at all or did I confuse the situation?

 

[CGoley]

Finally had some time to look at this.

As for the location of the ID, it will be in the A2 through A??? column. Each new row will have information in it. The A column has a "Defined Name" of "ID". Therefore, once an item is saved to the sheet, a new row will be created and the ID will need to increment. Therefore, the VBA code will need to look for the last entry and pick up that value and add +1 to it.

The code provided did not do anything when the form was opened and or saved. The ID did not populate.

I've tried this code, but this doesn't seem to work either.

ws2.Range("A" & DestRow) = ws2.Range("A" & DestRow).Offset(-1, 0) + 1

 

[HalfAce]

OK, I'm still not clear on what you're looking to do.
Are you wanting the textbox in the userform to load up with the value of the last used row in column A?

Or, add one to the last row, into the next row - and then load up the textbox with the new last value in Column A?

Or are you looking to add one to the textbox value and then enter that into the next row of column A?

(I'm sure we can get your userform to do whatever you want it to do, as soon as I figure out just what that is.)

 

[CGoley]

I'm wanting the Userform to load the last ID value +1 (12+1 or 13) in the ID textbox.

The other part would be that this new ID # is non-editing.

Does that clear up the confusion I created?

Craig

 

[CGoley]

Forgot to say, thus would increase per rown entry.

Example:
Row 2 = 45
Row 3 = 46
Row 4 = 47....etc.

Craig

 

[HalfAce]

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?

 

[CGoley]

HalfAce,

Thanks, that code worked like a charm. Locking the textbox did the trick to keep users from editing the value.

Craig

 

[HalfAce]

Most welcome. Glad it helped.

 

[CGoley]

The next project, is adding images (x3 or x4) to each item in the row. I've done some searching for attaching images to a userform, would you happen to know a good starting point for such a project?