Search code examples
vbaexcelupdatesuserform

Input new data based off user input on userform VBA

I am currently working a project (a userform in Excel 2016) that is intended to input new client information each month. I would like the user to search for each client with an ID number. After the user inputs an ID number through the userform (ID nubmer is also located in A:A on next sheet titled "Updates"). The code then loops though data on next sheet in A:A to locate the ID number.

I would like the new client information to update the row corresponding with the ID number input (e.g. user inputs ID number 12, 12 = Jon Doe in Row 2, so any new information entered by the user (after imputing ID number 12) will be pasted in Row 2).

enter image description here

enter image description here

'This sub locates the ID number corresponding to the name
'This section of code works well
Private Sub IDNumberBox_AfterUpdate()
'Checks to see if ID number exists
    If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.IDNumberBox.Value) = 0 
    Then
MsgBox "ID Not Found" & vbNewLine & "Please enter different ID"
    End If
'Lookup names based on ID number
    With Me
.txtfirstname = Application.WorksheetFunction.VLookup(CLng(Me.IDNumberBox), 
Sheet1.Range("IDandNAMES"), 2, 0)
.textlastname = Application.WorksheetFunction.VLookup(CLng(Me.IDNumberBox), 
Sheet1.Range("IDandNAMES"), 3, 0)
   End With
   End Sub


'This is the input button
'This code does not input any new data
Private Sub inputbutton_Click()
Dim currentrow As Long
Dim ws As Worksheet
Set ws = Worksheets("Updates")
lrow = ws.Cells(Rows.Count, 4).End(xlToRight).Select
   With ws
   If WorksheetFunction.CountIf(Sheet1.Range("A:A"), Me.IDNumberBox.Value) = True Then
    .Cells(1row, 4).Value = Me.txtupdate.Value
   '^this line of code should input data from textbox("txtupdate") in column D and in the row corresponding with the ID number input by user
    .Cells(lrow, 5).Value = Me.cmbfinancial.Value
    .Cells(lrow, 6).Value = Me.txtwcfin.Value
    .Cells(lrow, 7).Value = Me.cmbeducation.Value
    .Cells(lrow, 8).Value = Me.txtwcedu.Value
    .Cells(lrow, 9).Value = Me.cmbemploy.Value

I am new to VBA and any help would be greatly appreciated.

Solution

  • I found code that searches for an ID number, brings up client information and allows you to edit and/or input new information for the row of data corresponding to clients ID number.

    Dim currentrow As Long

Private Sub CommandButton2_Click()
Dim lastrow
Dim myfname As String
Dim ws As Sheet11
lastrow = Sheet11.Range("A" & Rows.Count).End(xlUp).row
myfname = Me.Reg8.Value
For currentrow = 2 To lastrow
If ws.Cells(currentrow, 1).Text = myfname Then
ws.Cells(currentrow, 68).Value = Me.Reg10.Value
ws.Cells(currentrow, 69).Value = Me.Reg11.Value
ws.Cells(currentrow, 10).Value = Me.Reg5.Value
ws.Cells(currentrow, 9).Value = Me.Reg6.Value
ws.Cells(currentrow, 70).Value = Me.Reg7.Value
End If
Next
MsgBox "Information has" & vbNewLine & "been updated"
End Sub

 Private Sub Reg8_AfterUpdate()
'Checks to see if ID number exists
  If WorksheetFunction.CountIf(Sheet11.Range("A:A"), Me.Reg8.Value) = 0 Then
  MsgBox "ID Not Found" & vbNewLine & "Please enter new Mentee informantion and submit"
Exit Sub
End If
'Lookup values based on ID number
 With Me
.Reg1 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 2, 0)
.Reg2 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 3, 0)
.Reg3 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 5, 0)
.Reg4 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 7, 0)
 .Reg5 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 10, 0)
 .Reg6 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 9, 0)
 .Reg7 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 70, 0)
 .Reg9 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 45, 0)
 .Reg10 = Application.WorksheetFunction.VLookup(CLng(Me.Reg8), Sheet11.Range("IDRangeTest"), 68, 0)
 End With
 End Sub