Update data on new line if person name is found already

[hsolanki]

Hi i have an code in combobox9 and i want also to update in column H when selected the names from the combobox9 and again update column H if the same person name found from the combobox on a new raw.

this is what i have so far and need some help. it needs to continue to look for the person name which is in column C and it need to check if that raw it is not already completed on column H until it finds a blank cell against the same person name.

If Me.ComboBox9.Value <> "" Then



If VBA.CVar(Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("C:C"), 0)) = True Then

MsgBox "Record Not found for this PO-Number", vbCritical

Exit Sub





Else

i = Application.Match(VBA.CStr(Me.ComboBox9.Value), sh.Range("C:C"), 0)



End If



lCol = Me.ComboBox9.Value

Set findvalue2 = sh.Range("C:C").Find(What:=lCol, LookIn:=xlValues)

If Not findvalue2 Is Nothing Then

adr = findvalue2.Address

Do



If findvalue2.Offset(0, -1).Value = Me.TextBox19 Then



sh.Unprotect "Bhaji2020"

findvalue2.Offset(0, 6).Value = Me.TextBox19.Value = ""

Exit Do

End If

Set findvalue2 = sh.Range("C:C").FindNext(findvalue2)

Loop While findvalue2.Address <> adr

Set findvalue2 = Nothing

End If

hope i am making sense in here

 

[dmt32]

Hi,
I have made an attempt to change code to do what I think you want but have now run out of time so hopefully what I have done will give you enough to continue with your project.

MAKE A COPY of your workbook & then DELETE ALL the code behind your userform

Now copy all the following code to your userforms code page

Option Explicit
Option Base 1
Dim sh As Worksheet
Dim rng   As Range
Const shPassword As String = "123"

Private Sub ComboBox9_Change()
    Dim RecordRow   As Long
    Dim i           As Integer
    Dim ControlsArr As Variant
   
    ControlsArr = Array(Me.TextBox14, Me.ComboBox5, Me.TextBox34, Me.TextBox15, _
                        Me.ComboBox6, Me.TextBox16, Me.TextBox17, Me.TextBox18, Me.TextBox19)
    'get record row
    RecordRow = Val(Me.ComboBox9.ListIndex) + 5
   
    If RecordRow > 4 Then Set rng = sh.Cells(RecordRow, 1) Else Set rng = Nothing
   
    For i = 1 To UBound(ControlsArr)
        With ControlsArr(i)
            If Not rng Is Nothing Then
            'populate controls from range
                .Text = sh.Cells(rng.Row, i).Text
                .Enabled = False
            Else
            'clear controls
                .Text = ""
                .Enabled = True
            End If
         End With
    Next i
   
    'enable Update RTW button
    With Me.CommandButton11
        .Enabled = Not rng Is Nothing And Len(Me.TextBox19.Text) = 0
        Me.TextBox19.Enabled = .Enabled
    End With
   
    'your name textbox
    With Me.TextBox19
        If .Enabled Then .SetFocus
    End With
   
    'update attendance log button
    With Me.CommandButton5
        .Enabled = Me.ComboBox9.ListIndex = -1
        'refresh button
        Me.CommandButton6.Enabled = .Enabled
    End With
End Sub


Private Sub CommandButton11_Click()
    Dim YourName As String
   
    YourName = Me.TextBox19.Text
    If YourName = "" Then
        MsgBox "Please confirm RTW & enter your name", vbCritical, "RTW Confirmation"
        Exit Sub
    End If
   
    On Error GoTo errhandler
    sh.Unprotect shPassword
  
    'update column I
    rng.Offset(, 8).Value = "Completed - " & YourName & " " & Date
    Me.CommandButton11.Enabled = False
    'inform user
    MsgBox "Record Updated", 64, "Updated"
   
errhandler:
    sh.Protect shPassword
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub


Private Sub CommandButton12_Click()
    'reset
    Me.ComboBox9.ListIndex = -1
End Sub

Private Sub UserForm_Initialize()

    Set sh = ThisWorkbook.Sheets("Attendance")
    'staff name
     With Me.ComboBox9
        .ColumnCount = 2
        .List = sh.Range(sh.Range("C5"), sh.Range("D" & sh.Rows.Count).End(xlUp)).Value
        .Font.Size = 11
        .Height = 26
        .Width = 160
        .Left = 350
        .Top = 216
        .TextColumn = 2
        .Style = fmStyleDropDownList
     End With
    
     'your name
     With Me.TextBox19
        .Height = Me.ComboBox9.Height
        .Width = 114
        .Left = 516
        .Top = Me.ComboBox9.Top
    End With
   
    With Me.Label62
        .Left = 400
        .Top = 198
    End With
   
    With Me.Label63
        .Left = 534
        .Top = 198
    End With
   
    Me.ComboBox5.List = Array("Sick", "Absent")
    Me.CommandButton11.Enabled = False
End Sub

NOTE the variables at the top of the code - These MUST sit at the VERY TOP of your forms code page OUTSIDE any procedure.

The updated code applies only to the Attendance Tab on your userform but if changes means it is now doing what you want then hopefully, you can work to include the code for other tabs.

Hope Helpful

Dave

 

[hsolanki]

Good Morning Dave. it has worked perfectly the way i wanted to and therefore i would like to say you are great and thank you for all your help.

i will finish the rest the other tab off.

Thank you once again

 

[dmt32]

Good Morning Dave. it has worked perfectly the way i wanted to and therefore i would like to say you are great and thank you for all your help.

i will finish the rest the other tab off.

Thank you once again

You are welcome & appreciate the feedback

Good luck with your project

Dave