Hi
i have main user-form 1 which has all the information including list box and when user select the name of the person it will find for duplicate and if user press Yes to duplicate msgbox another user-form 2 will popup requiring password and if user enters the correct password it will popup another user-form 3 which has comment box to be updated against initial duplicate find..
problem is that it only updates comment on 1st line row on sheet 1 (where the data updates) and NOT the actual duplicate it finds using staff ID number in column G in sheet1. it only finds first line which is within User-form1 listbox not the line you have selected in User-form1.
below it is the code which works fine within User-form1 and however it dose not work of what i wants to work when placed in user-form 3
please can someone help me resolve
Thank you in advance
i have main user-form 1 which has all the information including list box and when user select the name of the person it will find for duplicate and if user press Yes to duplicate msgbox another user-form 2 will popup requiring password and if user enters the correct password it will popup another user-form 3 which has comment box to be updated against initial duplicate find..
problem is that it only updates comment on 1st line row on sheet 1 (where the data updates) and NOT the actual duplicate it finds using staff ID number in column G in sheet1. it only finds first line which is within User-form1 listbox not the line you have selected in User-form1.
below it is the code which works fine within User-form1 and however it dose not work of what i wants to work when placed in user-form 3
VBA Code:
Private Sub CommandButton1_Click()
Dim p As Variant
Dim lCol As Variant
Dim response As Variant
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets("Jobs_Allocation")
Dim y As Long
Application.EnableCancelKey = xlDisabled
If TextBox1 > False Then
Unload Me
lCol = frmSelector.lstSelector.List(x, 3)
Set findvalue = Sheet1.Range("G:G").Find(What:=lCol, LookIn:=xlValues)
If Not findvalue Is Nothing Then
adr = findvalue.Address
Do
Sheet1.Unprotect Password:="123"
If findvalue.Offset(0, -1).Value = frmSelector.lstSelector.List(x, 2) Then
findvalue.Offset(0, 6).Value = Format(Now, "HH:MM:SS")
findvalue.Offset(0, 7).Value = TextBox1.Value
y = sh.Range("C" & Application.Rows.Count).End(xlUp).Row
Exit Do
End If
Set findvalue = Sheet1.Range("G:G").FindNext(findvalue)
Loop While findvalue.Address <> adr
Set findvalue = Nothing
End If
End If
If TextBox1.Value > 0 Then
MsgBox "You must enter the comment", vbCritical, "Error message"
Exit Sub
End If
End Sub
please can someone help me resolve
Thank you in advance