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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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,908
Messages
6,175,305
Members
452,633
Latest member
DougMo

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