Thread: VBA program for data entry - find the matching data to update

I have a few questions so I can get a better understanding of what you are trying to do. Based on your post, I gather you are using a UserForm which contains 10 Textboxes.

Textbox1 contains "Request #". You want to look in column A to see if "Request #" exists. If it does, you wish to replace the found "Request #" with the "Request #" from Textbox1 and (this is where I'm not clear) column B with Textbox2 value, column C with Textbox3 value, etc. to Column J with Textbox10 value.

If "Request #" is not found in Column A, you want to append Textbox1 through Textbox10 values to the first blank row in column A through Column J.

Is this correct? (A sample copy of your workbook would be helpful).

J

Originally Posted by jaslake

I have a few questions so I can get a better understanding of what you are trying to do. Based on your post, I gather you are using a UserForm which contains 10 Textboxes.

Textbox1 contains "Request #". You want to look in column A to see if "Request #" exists. If it does, you wish to replace the found "Request #" with the "Request #" from Textbox1 and (this is where I'm not clear) column B with Textbox2 value, column C with Textbox3 value, etc. to Column J with Textbox10 value.

If "Request #" is not found in Column A, you want to append Textbox1 through Textbox10 values to the first blank row in column A through Column J.

Is this correct? (A sample copy of your workbook would be helpful).

J

You are right J,

1) Click on Form Tab… and I fill in all the details in the form – and click OK
2) For example if the user had entered “123456” for the Req # field…
a. Then the script should find if ‘123456’ is available in Column A, and replace the form details on that row (Column A to J)
b. If there is no matching field them the script should find the next empty cell in Column A and append TextBox1 through Textbox10

Below is the link for the worksheet I’m working on.
http://www.2shared.com/file/6713070/...e_Tracker.html

Code:

Private Sub cmdOK_Click()
    ActiveWorkbook.Sheets("Req Status").Activate
       Cells.Find(What:=txtReqNum.Value, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
       :=xlPart, SearchOrder:=xlByRows, searchdirection:=xlNext, MatchCase:= _
       False, SearchFormat:=False).Activate
      
      If ActiveCell.Value = txtReqNum.Value Then
      ActiveCell.Offset(1, 0).Select
      
         Do
    Else IsEmpty(ActiveCell) = False Then 
         ActiveCell.Offset(1, 0).Select

    End If
    Loop Until IsEmpty(ActiveCell) = True
      
End Sub

I've slightly modified your exsting Sample Tracker workbook code for the OK button as follows:

Code:

Private Sub cmdOK_Click()
    ActiveWorkbook.Sheets("Req Status").Activate
    Range("A1").Select
    Do While Not IsEmpty(ActiveCell)
        ActiveCell.Offset(1, 0).Select

        If ActiveCell.Value = CLng(txtReqNum.Value) Then
            GoTo Update
        ElseIf IsEmpty(ActiveCell) Then
            GoTo Update
        End If
    Loop
'    ActiveCell.Select
Update:
    ActiveCell.Value = txtReqNum.Value
    ActiveCell.Offset(0, 1) = txtName.Value
    ActiveCell.Offset(0, 2) = TextDateAss.Value
    ActiveCell.Offset(0, 3) = TextLastUp.Value

    ActiveCell.Offset(0, 5) = txtTracking.Value

    ActiveCell.Offset(0, 6) = cboCommodity.Value
    ActiveCell.Offset(0, 7) = cboSubComm.Value
    If optInProcess = True Then
        ActiveCell.Offset(0, 8).Value = "IP"
        ActiveCell.Offset(0, 9).Value = ComboStatusReason.Value

    ElseIf optLostOpp = True Then
        ActiveCell.Offset(0, 8).Value = "LO"
    ElseIf optUnderNego = True Then
        ActiveCell.Offset(0, 8).Value = "UN"
    ElseIf OptSavings = True Then

        ActiveCell.Offset(0, 8).Value = "Savings"
        ActiveCell.Offset(0, 9).Value = ComSav.Value

    ElseIf OptNoScope = True Then
        ActiveCell.Offset(0, 8).Value = "NO"
    End If

    ActiveCell.Offset(0, 10) = TextTimeTaken.Value

   ' Unload Me
    Range("A1").Select
End Sub

If you wish to close the form after update, remove the comment code from Unload Me.
Hope this works for you.

J

Thank you so much J!!!! u saved me! it works... :-)

If i'm not asking for too much, can we modify the code to auto populate the values in the form - if the value matches on column 'A'

Ex: if '123456' is matching in column 'A', I would like to have all the values column from A to K to be auto filled in the form (then the user can change only what they want to change)- because the user wouldn't be changing all the fields. so if we can do a auto fill in the form, it will save lot of time.

I can call you if you want me to explain, any assistance you can provide will be of great help! :-)

Thanks again!

Try the following for auto-populating the user form when there is a hit on Req#.

Place this code in the code module for txtReqNum textbox. It should be at the very top of the module:

Code:

Public EnableEvents As Boolean

Also place this code in the same module as a procedure:

Code:

Private Sub txtReqNum_AfterUpdate()
    Dim LastRow As Long
    Dim ReqCell As Range

    Me.EnableEvents = False
    Sheets("Req Status").Select
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    For Each ReqCell In ActiveWorkbook.Sheets("Req Status").Range("A1", "A" & LastRow + 1).Cells
        If ReqCell.Value = CLng(txtReqNum.Value) Then
            ReqCell.Select
            GoTo Update
        ElseIf ReqCell.Value = 0 Then
            Exit Sub
        End If
    Next

Update:
    txtName.Value = ActiveCell.Offset(0, 1)
    TextDateAss.Value = ActiveCell.Offset(0, 2)
    TextLastUp.Value = ActiveCell.Offset(0, 3)
    txtTracking.Value = ActiveCell.Offset(0, 5)
    cboCommodity.Value = ActiveCell.Offset(0, 6)
    cboSubComm.Value = ActiveCell.Offset(0, 7)
    If ActiveCell.Offset(0, 8).Value = "IP" Then
        optInProcess = True
        ComboStatusReason.Value = ActiveCell.Offset(0, 9).Value
    ElseIf ActiveCell.Offset(0, 8).Value = "LO" Then
        optLostOpp = True

    ElseIf ActiveCell.Offset(0, 8).Value = "UN" Then
        optUnderNego = True
    ElseIf ActiveCell.Offset(0, 8).Value = "Savings" Then
        ComSav.Value = ActiveCell.Offset(0, 9).Value
        OptSavings = True
    ElseIf ActiveCell.Offset(0, 8).Value = "NO" Then
        OptNoScope = True
    End If
    TextTimeTaken.Value = ActiveCell.Offset(0, 10)
    Me.EnableEvents = True

End Sub

Let me know how you make out.

J

You will get that error if

Code:

Public EnableEvents As Boolean

is not in the right place in the code. Make certain it's at the very top of the code module for txtReqNum textbox before ANYcode lines in the entire module . It will look like this:

Code:

Public EnableEvents As Boolean

Private Sub optInProcess_Change()
    If optInProcess = True Then
        ComboStatusReason.Enabled = True
        ComboStatusReason.Visible = True

    Else
        ComboStatusReason.Enabled = False
        ComboStatusReason = ""
        ComboStatusReason.Visible = False
    End If
End Sub

This should fix the problem. You can run the process without these lines of code but populating the UserForm causes all kinds of other macros to fire. These code lines prevent other macros from firing during the population.

Let me know how you make out.

J

Thanks J! it works now... i'm little new to this, i'm sorry :-(

When i click on OK in the form with some changes the data is not getting updated in the data base. should i add "Private Sub cmdOK_Click()" in the same code...

No need to apologize, I've much to learn also. My worksheet appears to update as it should. Can you tell me what is not updating? You may need to send me your current file so I can look at it.

J

I reread your second to last post and see you said

And also "Private Sub cmdOK_Click()" is missing...

. That's the code we worked on initially. Again, you said

Thank you so much J!!!! u saved me! it works... :-)

So, my question is "What happened to it?" Did you overwrite it with the auto populate code? If so, that was not my intention. The Private Sub cmdOK_Click() macro and the Private Sub txtReqNum_AfterUpdate()
macro should both exist in their entirety.
Sorry for the confusion!
Let me know if this fixes the update problem.

J