UserForm for Similar Entries

Wasn't quite sure how to title this thread, but I'll try and explain what I'm trying to do the best I can.

I've built a spreadsheet to act as a database for customer information. This data includes company names, identifier codes, contact names, addresses, phone numbers, emails, blah blah everything you can think of. All of this information is arranged with a PivotTable to make it easy to access.

I built a UserForm to help ease data entry, but I'm running in to a UX problem. For example if I wanted to add a new contact for a customer we already have information for, I would need to input ALL the customer information (addresses, account managers etc) just to add an extra person. This adds a whole lot of extra legwork to find all the info.

I'm trying to edit my UserForm in a way so that the user can load a current customer's information, over type and save it to a new line in the source data.

Here's a screenshot of my current userform, and below is the code I'm currently using to input new data. Please feel free to ask questions, I probably haven't explained it very well! I don't know a huge amount about VBA coding, so explanations on how to implement the code would be very helpful.

Thanks,
Dom

VBAHelp.PNG

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DATA")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

If Trim(Me.ComboCode.Value) = "" Then
  Me.ComboCode.SetFocus
  MsgBox "Please chose a code"
  Exit Sub
End If

'copy the entered data to the database

With ws

  .Cells(iRow, 1).Value = Me.ComboCode.Value
  .Cells(iRow, 3).Value = Me.TextLocation.Value
  .Cells(iRow, 5).Value = Me.TextCompName.Value
  .Cells(iRow, 6).Value = Me.TextCustNumber.Value
  .Cells(iRow, 7).Value = Me.TextAffiliation.Value
  .Cells(iRow, 8).Value = Me.TextGroup.Value
  .Cells(iRow, 10).Value = Me.ComboAccMgr.Value
  .Cells(iRow, 12).Value = Me.ComboTitle.Value
  .Cells(iRow, 13).Value = Me.TextForename.Value
  .Cells(iRow, 14).Value = Me.TextSurname.Value
  .Cells(iRow, 15).Value = Me.TextPosition.Value
  .Cells(iRow, 17).Value = Me.ComboAddType.Value
  .Cells(iRow, 18).Value = Me.TextAdd1.Value
  .Cells(iRow, 19).Value = Me.TextAdd2.Value
  .Cells(iRow, 20).Value = Me.TextAdd3.Value
  .Cells(iRow, 21).Value = Me.TextAdd4.Value
  .Cells(iRow, 22).Value = Me.TextAdd5.Value
  .Cells(iRow, 23).Value = Me.TextPostcode.Value
  .Cells(iRow, 24).Value = Me.TextPhone.Value
  .Cells(iRow, 25).Value = Me.TextMobile.Value
  .Cells(iRow, 26).Value = Me.TextFax.Value
  .Cells(iRow, 27).Value = Me.TextEmail.Value
  .Cells(iRow, 28).Value = Me.TextWebsite.Value
  .Cells(iRow, 33).Value = Me.TextComment.Value
  
'Opt Out of Marketing
  If Me.TickYes Then
    .Cells(iRow, 34).Value = "Yes"
  ElseIf Me.TickNo Then
    .Cells(iRow, 34).Value = "No"
  Else
  .Cells(iRow, 34).Value = "No"
  
End If

  
'Manufacturer Option Buttons

'PANESCO
  If Me.OptPanYes Then
     .Cells(iRow, 29).Value = "Yes"
  ElseIf Me.OptPanPot Then
    .Cells(iRow, 29).Value = "Potential"
  Else
    .Cells(iRow, 29).Value = "No"
End If

'FARMHOUSE
  If Me.OptFarYes Then
    .Cells(iRow, 30).Value = "Yes"
  ElseIf Me.OptFarpot Then
    .Cells(iRow, 30).Value = "Potential"
  Else
    .Cells(iRow, 30).Value = "No"
End If

'PASTRY ROOM
  If Me.OptTPRYes Then
    .Cells(iRow, 31).Value = "Yes"
  ElseIf Me.OptTPRPot Then
    .Cells(iRow, 31).Value = "Potential"
  Else
    .Cells(iRow, 31).Value = "No"
End If

'LAUNIS
  If Me.OptLauYes Then
     .Cells(iRow, 32).Value = "Yes"
  ElseIf Me.OptLauPot Then
    .Cells(iRow, 32).Value = "Potential"
  Else
    .Cells(iRow, 32).Value = "No"
End If

End With

'clear the data after entry
Application.ScreenUpdating = False
Unload Me
AddEntry.Show
Application.ScreenUpdating = True

'refresh every pivot table
ThisWorkbook.RefreshAll

End Sub

Private Sub CmdCancel_Click()

'Close the form with prompt

Sure = MsgBox("Are you sure you want to cancel this entry?", vbYesNo)
If Sure = vbYes Then

  Unload Me
  
  End If

End Sub

Private Sub CmdReset_Click()

'Clear all information on UserForm

Sure = MsgBox("Are you sure you want to clear all the details?", vbYesNo)
If Sure = vbYes Then

Application.ScreenUpdating = False
Unload Me
AddEntry.Show
Application.ScreenUpdating = True

End If

End Sub

Private Sub ResetSelection_Click()

'Reset the manufacturer selections
Me.OptPanYes.Value = False
Me.OptPanPot.Value = False
Me.OptFarYes.Value = False
Me.OptFarpot.Value = False
Me.OptTPRYes.Value = False
Me.OptTPRPot.Value = False
Me.OptLauYes.Value = False
Me.OptLauPot.Value = False


End Sub

Private Sub UserForm_Initialize()

'Set Code dropdown to 2 columns
    With ComboCode
        .ColumnCount = 2
        .ColumnWidths = "1.3 cm;2.7 in;"
        .ListWidth = "4 in"
        .List = Range("Code").Value
        .AddItem "", 0
    End With
    
'Set dropdown for Account Manager
    With ComboAccMgr
        .List = Range("AccMgr").Value
        .AddItem "", 0
    End With
    
'Set dropdown for Address Type to named range
    With ComboAddType
        .List = Range("AddType").Value
        .AddItem "", 0
    End With
    
'Set dropdown for Title to named range
    With ComboTitle
        .List = Range("Title").Value
        .AddItem "", 0
    End With

End Sub