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.
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
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.
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