Hi can anybody fix me this formula which is for the User form
Sub Addme()
'declare the variables
Dim Addme As Range
Dim x As Integer
'error handler
On Error GoTo errHandler:
'find the next black row in the database
Sheet1.Unprotect Password:="Bhaji2019"
With Sheet1.Range("c710000") ' Simply Change the range to suit
.Locked = False
.FormulaHidden = False
If Application.WorksheetFunction.CountIf(Sheet1.Range("G:G").Me.cboBooked) > 0 Then
MsgBox "on a Job"
Me.cboBooked.Value = ""
Exit Sub
Else: Addme
End If
End With
Set Addme = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'loop through multi selected items and add them to the database
For x = 0 To Me.lstSelector.ListCount - 1
If Me.lstSelector.Selected(x) Then
Addme = Me.cbodatetime
Addme.Offset(0, 1) = Me.lstSelector.List(x)
Addme.Offset(0, 2) = Me.cboBooked.Value
Addme.Offset(0, 3) = Me.txtJob.Value
Addme.Offset(0, 4) = Me.lstSelector.List(x, 3)
Addme.Offset(0, 5) = Me.lstSelector.List(x, 4)
Addme.Offset(0, 6) = Me.lstSelector.List(x, 5)
Addme.Offset(0, 7) = Me.lstSelector.List(x, 6)
Addme.Offset(0, 8) = Me.lstSelector.List(x, 7)
Addme.Offset(0, 9) = Me.lstSelector.List(x, 8)
Set Addme = Addme.Offset(1, 0)
End If
Next x
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please Contact Harin Solanki"
End Sub
VBA Code:
'declare the variables
Dim Addme As Range
Dim x As Integer
'error handler
On Error GoTo errHandler:
'find the next black row in the database
Sheet1.Unprotect Password:="Bhaji2019"
With Sheet1.Range("c710000") ' Simply Change the range to suit
.Locked = False
.FormulaHidden = False
If Application.WorksheetFunction.CountIf(Sheet1.Range("G:G").Me.cboBooked) > 0 Then
MsgBox "on a Job"
Me.cboBooked.Value = ""
Exit Sub
Else: Addme
End If
End With
Set Addme = Sheet1.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'loop through multi selected items and add them to the database
For x = 0 To Me.lstSelector.ListCount - 1
If Me.lstSelector.Selected(x) Then
Addme = Me.cbodatetime
Addme.Offset(0, 1) = Me.lstSelector.List(x)
Addme.Offset(0, 2) = Me.cboBooked.Value
Addme.Offset(0, 3) = Me.txtJob.Value
Addme.Offset(0, 4) = Me.lstSelector.List(x, 3)
Addme.Offset(0, 5) = Me.lstSelector.List(x, 4)
Addme.Offset(0, 6) = Me.lstSelector.List(x, 5)
Addme.Offset(0, 7) = Me.lstSelector.List(x, 6)
Addme.Offset(0, 8) = Me.lstSelector.List(x, 7)
Addme.Offset(0, 9) = Me.lstSelector.List(x, 8)
Set Addme = Addme.Offset(1, 0)
End If
Next x
'error block
On Error GoTo 0
Exit Sub
errHandler:
MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _
& Err.Number & vbCrLf & Err.Description & vbCrLf & _
"Please Contact Harin Solanki"
End Sub