Error with VBA code

[hsolanki]

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

 

[hsolanki]

hi i have tried debugging but no luck if you kindly do this if you have time as it may take you 2min while i am been trying this for last 3 weeks lol

 

[FormR]

if you kindly do this if you have time

For me to debug, you need to explain for the sample workbook you uploaded, what exactly needs to be entered into which text boxes and what button gets pressed and what happens versus what you expect should happen, along with a description of why.

 

[hsolanki]

Hi Basically on sheet 1 when you press "Allocation Jobs" user form will open up and then when you select staff member from the user form list and to booked them by entering to a Job with the Job numbers and it will then add all the selected names. which are taken from Sheet 2 staff list and will also booked status in Sheet2.

so i what i would like to do is the if user has already for example Booked John, Dev and Liam on a Job number 5225 which is on sheet 1 and if users tries to Book them on a another Job via user form then once entered a Job number and when pressing Green (RUN) button on user form mesbox should says already allocated on a Job, do you still wish to allocate another Job by Yes/No and if pressed Yes then add data on sheet 1 and if user says No then nothing should happens.

i have managed to worked around the code, however it will only works with the Job numbers and No not the people

If Application.WorksheetFunction.CountIf(Sheet1.Range("F:F"), Me.txtJob) > 0 Then
MsgBox "on a Job"
Me.txtJob.Value = ""
Exit Sub
Else

End If

i hope i am making a sense here

Once again

Thank you so much for taking your time to help me

 

[FormR]

however it will only works with the Job numbers and No not the people

Sounds like you probably want to use Countifs() and include the range and criteria for the persons name?

 

[hsolanki]

I am sorry but how do i write that in vba Code?

i have amended below code but then as soon as submit a Run button it says already on a Job even though he is not allocated on any Jobs.

If Application.WorksheetFunction.CountIfs(Sheet1.Range("H:H"), Me.reg5.Value) > 0 Then
MsgBox "on a Job"
Me.reg5.Value = ""
Exit Sub
Else

End If

 

[FormR]

Try to explain clearly, using excel terms, when someone is deemed to already be on a job.

i.e. when column A is equal to textbox1 and column B is equal to textbox2 etc.

 

[hsolanki]

Hi I understand your point

however i have not design this workbook myself and learned from Vidoes and also i am not an expert on this hence i am really really struggling with this.

and i am sorry for this. i have tried below but it only stopping if you put the with same Jon number but that not i want i want people instead of Jobs.

If Application.WorksheetFunction.CountIfs(Sheet1.Range("F:F"), Me.txtJob) > 0 Then
MsgBox "on a Job"
Me.txtJob.Value = ""
Exit Sub
Else

End If

 

[FormR]

I understand your point

Hi, I'm not sure you do, assume we know nothing about your workbook (forget the fact you have uploaded it). Explain the logic for deciding if the "job" you are trying to add is a duplicate? i.e. specifically which conditions must hold true. Mention both the sheet ranges involved and the object names on the form.

 

[hsolanki]

Hi
no i'm trying to find Duplicate names prior to submitting data from User form via Run (command button), if the same name

is already on Sheet1 in column (H) or find using the staff ID in column (G)

 

[FormR]

Please just specifically state which column(s) on the sheet need to equal which control(s) on your form for the job to be considered a duplicate.

No pictures, no links to the workbook, just a simple description of the column references and control names.