Additional code require for currently working code

[ipbr21054]

Morning all,

The supplied code below i believe is what you require & is currently in use BUT i would like an extra piece of code added to it if i may ask please.
This is how it currently works,
I select ADD NEW CUSTOMER TO DATABASE & complete all fields.
I then select SAVE NEW CUSTOMER TO DATABASE,this then saves the data into my worksheet.
This currently works fine.

The extra piece of code i require is as follows,
Once all the fields are completed & SAVE NEW CUSTOMER TO DATABASE is selected BEFORE it is saved to the worksheet check column A to see if there is a match.
The two items that need to be checked would be Customers name on user form & Customers name which is in Column A on my worksheet.
It would work something like this,
If no match is found then allow the save to go ahead,which is what currently happens.
If there is a match then maybe have message box appear saying customer exists etc.
Allow me to then edit that customers name on the user form etc and then be able to select SAVE NEW CUSTOMER TO DATABASE for the save to go ahead.
This will then allow me NOT to have duplicated customers names in my worksheet.
Check should allow upper case / lower case check and not be 100% specific match etc, Dave Tomms / DAVE TOMMS etc etc

Many thanks & hope i have not over explained it ?

Private Sub UpdateRecord_Click()    Dim i As Integer
    Dim IsNewCustomer As Boolean
    Dim Msg As String
    
    
    IsNewCustomer = CBool(Me.UpdateRecord.Tag)
    
    Msg = "CHANGES SAVED SUCCESSFULLY"
    
    If IsNewCustomer Then
    'New record - check all fields entered
    If Not IsComplete(Form:=Me) Then Exit Sub
        r = StartRow
        Msg = "NEW CUSTOMER SAVED TO DATABASE"
        ws.Range("A6").EntireRow.Insert
        ResetButtons Not IsNewCustomer
        Me.NextRecord.Enabled = True
    End If
    
    On Error GoTo myerror
    Application.EnableEvents = False
    'Add / Update Record
    For i = 1 To UBound(ControlNames)
        With Me.Controls(ControlNames(i))
            'check if date value
            If IsDate(.Text) Then
                ws.Cells(r, i).Value = DateValue(.Text)
            ElseIf i = 15 Then
                ws.Cells(r, i).Value = CDbl(.Text)
            Else
                ws.Cells(r, i).Value = UCase(.Text)
            End If
                ws.Cells(r, i).Font.Size = 11
        End With
    Next i
    
    If IsNewCustomer Then Call ComboBoxCustomersNames_Update
        
    ThisWorkbook.Save
    
    'tell user what happened
    MsgBox Msg, 48, Msg
    
myerror:
Application.EnableEvents = True
'something went wrong tell user
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub


Sub ResetButtons(ByVal Status As Boolean)
    
    With Me.NewRecord
        .Caption = IIf(Status, "CANCEL", "ADD NEW CUSTOMER TO DATABASE")
        .BackColor = IIf(Status, &HFF&, &H8000000F)
        .ForeColor = IIf(Status, &HFFFFFF, &H0&)
        .Tag = Not Status
    Me.ComboBoxCustomersNames.Enabled = CBool(.Tag)
    End With
    
    With Me.UpdateRecord
        .Caption = IIf(Status, "SAVE NEW CUSTOMER TO DATABASE", "SAVE CHANGES FOR THIS CUSTOMER")
        .Tag = Status
    End With
End Sub

 

[ipbr21054]

Sorted.
You beat me to it,i was getting it sorted but the Row 6 would appear again.

Now working as requested,many thanks

 

[dchaney]

Glad to help

 

[ipbr21054]

Not sure on how to tackle this.
The worksheet has a drop down list in various cells on Row 6
So A6 I manually type customers name.
B6 has a drop down list of which I make a selection then that selection is shown in B6
My userform has text boxes to match the cells on the worksheet A6 B6 C6 etc etc but no drop down list option.
Can I add 1 drop down list at the top of the userform to work like this.
I select text box VEHICLE on the userform & now the drop down list has the vehicle selections loaded . On making a selection from the list the selected item is the entered into that text box VEHICLE If I then select the text box named ITEM SUPPLIED the drop down list is now loaded with the item supplied options. Same again, on making a selection from the list the selected item is then entered into text box ITEM SUPPLIED.