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

Alrabia

New Member
Joined
May 14, 2018
Messages
6
Hi All,

Greetings!

I've been working on a user form to track my inventory. I am using the following code below to add new data but it does not automatically increment the ID. My data range is from Sheet 7 - B9:J2000 and my ID column header is in B8.
I'll need help to correct this code to have it increase automatically.

Any help would be greatly appreciated.


Private Sub cmdAddNew_Click()
'dimention the variable
Dim ListSH As Worksheet
Dim Addme As Range
'set the variable
Set ListSH = Sheet7
'error handler
On Error GoTo errHandler:
'set variable for the destination
Set Addme = ListSH.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'hold in memory and stop screen flicker
Application.ScreenUpdating = False
If Me.Detail8 = "" Or Me.Detail1 = "" Or Me.Detail2 = "" Then
MsgBox "There is insufficient data, Please return and add the needed information"
Exit Sub
End If
'send the values to the database
With ListSH
'add the unique reference ID then all other values
Addme.Offset(0, -1) = ListSH.Range("B9").Value + 1
Addme.Value = Me.Detail14.Value
Addme.Offset(0, 0) = Format(Me.Detail8.Value, "dd-mmm-yy")
Addme.Offset(0, 1) = Detail1.Value
Addme.Offset(0, 2) = Detail2.Value
Addme.Offset(0, 3) = Detail3.Value
Addme.Offset(0, 4) = Format(Me.Detail4.Value, "dd-mmm-yy")
Addme.Offset(0, 5) = Format(Me.Detail5.Value, "dd-mmm-yy")
Addme.Offset(0, 6) = Detail6.Value
Addme.Offset(0, 7) = Detail7.Value
Addme.Offset(0, 8) = Format(Me.Detail9.Value, "dd-mmm-yy")


-A
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
If your IDs are numbers try
Code:
Addme.Offset(0, -1) = Addme.Offset(-1, -1) + 1
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,223,270
Messages
6,171,103
Members
452,379
Latest member
IainTru

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top