Update data on new line if person name is found already

H

hsolanki

Board Regular
Joined
Jan 16, 2020
Messages
204
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code: 
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Hi Can anyone help me resolve the problem please as i am really stuck. i can provide sample book if required

thank you
 
Upvote 0
Hi,
sounds like you probably need to consider using Rang.FindNext Method

Have a read here:Range.FindNext method (Excel)
There are some code examples you can take & may be able to adapt to meet your specific requirement

Dave
 
Upvote 0
Hi thank you for your promote respond. sorry i new to VBA and i have tried and doe not know what im doing wrong.

would you kindly be able to help me with the code? if you can :)
 
Upvote 0
Hi,
post all your code with clear explanation of results you require - I am time limited this week but will have a look - there are others here who may also be able to assist.

Dave
 
Upvote 0
Hi Thank you so much. i have placed the code below. basically on tab 4 call (Attendance). for example on attendance tab there guy call Emanuele on cell D6 and again on D11 so bascially i want an code when you update RTW his record from the user form Attendance, code needs to find if cell i6 is completed already against cel D6 to match and loop for the same name to update next cell which would be the i11 which will match against D11.

i hope i am making a sense.

VBA Code: 
Private Sub ComboBox9_Change()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim i As Long

Dim x As Long

Dim findvalue

Dim adr As String

Dim lCol As Variant



On Error GoTo errhandler:



If Me.ComboBox9.Value <> "" Then



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

MsgBox "Record Not found", vbCritical

Exit Sub





Else

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

End If



Me.OptionButton3.Enabled = False

Me.CommandButton5.Enabled = False

Me.CommandButton6.Enabled = False



Me.TextBox14.Value = sh.Range("A" & i).Value

Me.TextBox14.Enabled = False

Me.TextBox14.BackColor = RGB(155, 295, 155)



Me.ComboBox5.Value = sh.Range("B" & i).Value

Me.ComboBox5.Enabled = False

'change the button back color

Me.ComboBox5.BackColor = RGB(155, 295, 155)



Me.TextBox34.Value = sh.Range("C" & i).Value

Me.TextBox34.Enabled = False

Me.TextBox34.BackColor = RGB(155, 295, 155)



Me.TextBox15.Value = sh.Range("D" & i).Value

Me.TextBox15.Enabled = False

'change the button back color

Me.TextBox15.BackColor = RGB(155, 295, 155)



Me.ComboBox6.Value = sh.Range("E" & i).Value

Me.ComboBox6.Enabled = False

'change the button back color

Me.ComboBox6.BackColor = RGB(155, 295, 155)



Me.TextBox16.Value = sh.Range("F" & i).Value

Me.TextBox16.Enabled = False

'change the button back color

Me.TextBox16.BackColor = RGB(155, 295, 155)



Me.TextBox17.Value = sh.Range("G" & i).Value

Me.TextBox17.Enabled = False

'change the button back color

Me.TextBox17.BackColor = RGB(155, 295, 155)



Me.TextBox18.Value = sh.Range("H" & i).Value

Me.TextBox18.Enabled = False

'change the button back color

Me.TextBox18.BackColor = RGB(155, 295, 155)



If Me.TextBox19.Value = sh.Range("i" & i).Value = True Then

Me.TextBox19.Enabled = True

Me.TextBox19.BackColor = &H80000005

Else

Me.TextBox19.Enabled = False

'Me.CommandButton11.Enabled = False

Me.TextBox19.BackColor = RGB(155, 295, 155)

Me.TextBox19.Value = sh.Range("i" & i).Value

End If







Else



Me.TextBox14.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""

Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



End If



Exit Sub



errhandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin", vbCritical, "Error Message"

End Sub





Private Sub CommandButton11_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim n As Long

Dim answer As String

Dim findvalue

Dim adr As String

Dim lCol As Variant



Application.EnableCancelKey = xlDisabled



Application.ScreenUpdating = False

Application.EnableEvents = False



On Error GoTo errhandler:



If Me.ComboBox9.Value = "" Then

MsgBox "Please select the staff name for RTW", vbCritical, "Select name"

Exit Sub

End If



If Me.TextBox19.Value = "" Then

MsgBox "Please confirm RTW & enter your name", vbCritical, "RTW Confirmation"

Exit Sub

End If





n = Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("D:D"), 0)



answer = MsgBox(Me.TextBox19.Value & Chr(10) & Chr(10) & Me.TextBox15.Value & Chr(10) & Chr(10) & "Would you like to update RTW and confirmation for above person?", vbQuestion + vbYesNo, "Confirm to Add")



If answer = vbNo Then

Exit Sub

End If



Dim sname As String, sID As String, rng As Range

Dim firstAddress As String



sname = Me.ComboBox9

sID = Me.TextBox34



With sh.Range("D:D")

Set rng = .Find(What:=sname, LookIn:=xlValues)

' rng is col G

If Not rng Is Nothing Then

firstAddress = rng.Address

Do

If rng.Offset(0, -1) = sID Then ' col F

sh.Unprotect "123"

rng.Offset(0, 7) = Format(Now, "HH:MM:SS") ' col M

'Debug.Print rng.Address

End If

Set rng = .FindNext(rng)

Loop While rng.Address <> firstAddress

End If

End With









sh.Range("I" & n).Value = Me.TextBox19.Value



'''''''''' Clear boxes



Me.TextBox4.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""



Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



sh.Protect "123"



MsgBox "RTW Updated Successfully!", vbInformation





Unload Me



Worksheets("Attendance").Activate

Worksheets("Attendance").Cells(1, 3).Select



Application.EnableEvents = True

Application.ScreenUpdating = True



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 Admin"



End Sub
 

Attachments

  • vba excel help needed.jpg
    vba excel help needed.jpg
    207 KB · Views: 7
  • vba excel user form update.jpg
    vba excel user form update.jpg
    178.1 KB · Views: 7
Upvote 0
only managed a quick look but maybe something like this?

VBA Code: 
Private Sub CommandButton11_Click()
   
    Dim sh          As Worksheet
    Dim rng         As Range
    Dim StaffName   As String, StaffID As String
    Dim firstAddress As String
   
   
    On Error GoTo errhandler
   
    StaffName = Me.ComboBox9.Text
   
    StaffID = Me.TextBox34.Text
   
     Set sh = ThisWorkbook.Sheets("Attendance")

         sh.Unprotect "123"
        
        Set rng = sh.Columns.Find(What:=StaffName, LookIn:=xlValues, lookat:=xlWhole)
       
        If Not rng Is Nothing Then
           
            firstAddress = rng.Address
           
            Do
                If rng.Offset(0, -1) = StaffID Then
                With rng.Offset(0, 5)
                    If Len(.Value) = 0 Then .Value = "Completed " & Date
                End With
            End If
           
            Set rng = rng.FindNext(rng)
            If rng Is Nothing Then Exit Do
           
        Loop While rng.Address <> firstAddress
       
        Else
            'search text not found
            Err.Raise 744
    End If
   
End With


errhandler:
    sh.Protect "123"
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Suggestion untested & may need to be adjusted to meet specific project need which is why I removed all the other stuff in the code to help you getting this bit right -

If still having difficulty, place copy of your workbook with dummy data in a dropbox & provide a link to it - may be able to find a moment this week to have a play

Dave
 
Last edited:
Upvote 0
Hi it did not work. i uploaded an link below for sample of the book and also an complete code

VBA Code: 
Option Explicit



Private Sub ComboBox9_Change()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim i As Long

Dim x As Long

Dim findvalue

Dim adr As String

Dim lCol As Variant



On Error GoTo errhandler:



If Me.ComboBox9.Value <> "" Then



If VBA.CVar(Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("D:D"), 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("D:D"), 0)

End If



Me.OptionButton3.Enabled = False

Me.CommandButton5.Enabled = False

Me.CommandButton6.Enabled = False



Me.TextBox14.Value = sh.Range("A" & i).Value

Me.TextBox14.Enabled = False

Me.TextBox14.BackColor = RGB(155, 295, 155)



Me.ComboBox5.Value = sh.Range("B" & i).Value

Me.ComboBox5.Enabled = False

'change the button back color

Me.ComboBox5.BackColor = RGB(155, 295, 155)



Me.TextBox34.Value = sh.Range("C" & i).Value

Me.TextBox34.Enabled = False

Me.TextBox34.BackColor = RGB(155, 295, 155)



Me.TextBox15.Value = sh.Range("D" & i).Value

Me.TextBox15.Enabled = False

'change the button back color

Me.TextBox15.BackColor = RGB(155, 295, 155)



Me.ComboBox6.Value = sh.Range("E" & i).Value

Me.ComboBox6.Enabled = False

'change the button back color

Me.ComboBox6.BackColor = RGB(155, 295, 155)



Me.TextBox16.Value = sh.Range("F" & i).Value

Me.TextBox16.Enabled = False

'change the button back color

Me.TextBox16.BackColor = RGB(155, 295, 155)



Me.TextBox17.Value = sh.Range("G" & i).Value

Me.TextBox17.Enabled = False

'change the button back color

Me.TextBox17.BackColor = RGB(155, 295, 155)



Me.TextBox18.Value = sh.Range("H" & i).Value

Me.TextBox18.Enabled = False

'change the button back color

Me.TextBox18.BackColor = RGB(155, 295, 155)



If Me.TextBox19.Value = sh.Range("i" & i).Value = True Then

Me.TextBox19.Enabled = True

Me.TextBox19.BackColor = &H80000005

Else

Me.TextBox19.Enabled = False

'Me.CommandButton11.Enabled = False

Me.TextBox19.BackColor = RGB(155, 295, 155)

Me.TextBox19.Value = sh.Range("i" & i).Value

End If




Else



Me.TextBox14.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""

Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



End If



Exit Sub



errhandler:

MsgBox "An Error has Occurred " & vbCrLf & "The error number is: " _

& Err.Number & vbCrLf & Err.Description & vbCrLf & _

"Please Contact Admin", vbCritical, "Error Message"

End Sub



Private Sub CommandButton11_Click()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim n As Long

Dim answer As String

Dim findvalue

Dim adr As String

Dim lCol As Variant



Application.EnableCancelKey = xlDisabled


Application.ScreenUpdating = False

Application.EnableEvents = False



On Error GoTo errhandler:



If Me.ComboBox9.Value = "" Then

MsgBox "Please select the staff name for RTW", vbCritical, "Select name"

Exit Sub

End If


If Me.TextBox19.Value = "" Then

MsgBox "Please confirm RTW & enter your name", vbCritical, "RTW Confirmation"

Exit Sub

End If



n = Application.Match(VBA.CVar(Me.ComboBox9.Value), sh.Range("D:D"), 0)



answer = MsgBox(Me.TextBox19.Value & Chr(10) & Chr(10) & Me.TextBox15.Value & Chr(10) & Chr(10) & "Would you like to update RTW and confirmation for above person?", vbQuestion + vbYesNo, "Confirm to Add")



If answer = vbNo Then

Exit Sub

End If



Dim sname As String, sID As String, rng As Range

Dim firstAddress As String



sname = Me.ComboBox9

sID = Me.TextBox34



With sh.Range("D:D")

Set rng = .Find(What:=sname, LookIn:=xlValues)

' rng is col D

If Not rng Is Nothing Then

firstAddress = rng.Address

Do

If rng.Offset(0, -1) = sID Then

sh.Unprotect "Bhaji2020"

sh.Range("I" & n).Value = Me.TextBox19.Value



End If

Set rng = .FindNext(rng)

Loop While rng.Address <> firstAddress

End If

End With



'''''''''' Clear boxes


Me.TextBox4.Value = ""

Me.TextBox15.Value = ""

Me.TextBox16.Value = ""

Me.TextBox17.Value = ""

Me.TextBox18.Value = ""

Me.TextBox19.Value = ""

Me.TextBox34.Value = ""



Me.ComboBox5.Value = ""

Me.ComboBox6.Value = ""

Me.ComboBox9.Value = ""



sh.Protect "Bhaji2020"



MsgBox "RTW Updated Successfully!", vbInformation



Unload Me


Worksheets("Attendance").Activate

Worksheets("Attendance").Cells(1, 3).Select



Application.EnableEvents = True

Application.ScreenUpdating = True



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 Admin"



End Sub



Private Sub UserForm_Activate()

Dim sh As Worksheet

Set sh = ThisWorkbook.Sheets("Attendance")

Dim n As Long



Me.ComboBox9.Clear

Me.ComboBox9.AddItem ""



For n = 5 To sh.Range("D" & Application.Rows.Count).End(xlUp).Row

Me.ComboBox9.AddItem sh.Range("D" & n).Value

Next n



Application.ScreenUpdating = False

Application.EnableEvents = False




With Me.ComboBox5

.AddItem "Sick"

.AddItem "Absent"

End With



End Sub


Test book V1.xlsm
 
Upvote 0
Hi,
I can see the issue - search code needs to be located in the Combobox change event to find correct record.
If I can find time, will see if I can update it for you

Dave.
 
Upvote 0
Hi Excellent, many thanks for all your time and please if you could correct the code as i have been struggling for past few weeks.

yes you are right it needs to loop within the combo prior to updating the data sheet.

Thank you once again
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Locked
  • Question Question
combobox to find value using loop
Replies
1
Views
562
Fluff
Fluff
Agnarr
  • Question Question
VBA problem with creating new sheets
Replies
0
Views
800
Agnarr
Agnarr
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
744
sspatriots
S
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
534
sspatriots
S
A
  • Question Question
VBA problem with setting data source range
Replies
1
Views
1K
Akbarov
A

Forum statistics

Threads
1,223,243
Messages
6,170,964
Members
452,371
Latest member
Frana

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top