Find-Change-Delete

[TheGreatest]

Hi,

I have a one workseheet on my workbook called AddressBook and I am using this as address database. I have 2 UserForms one for New Address entry and one for address search.Everything is working fine up to here. What I want to do is add 2 new commandbutton to the search UserForm 1 for modifying and 1 for deleting a adress details.If anybody has a code to do this would be greatly appreciated...

Thanks in advance

[Zack Barresse]

Hi there,

Would it be a problem if you attached a (zipped) version of your file? If you need to strip information due to security/privacy please do so. You can attach a picture of it if you can't zip it (as a JPEP).

[TheGreatest]

Hi again,

The file is attached, It can do the basic add new contact and Search for contact. All I want to do is add 2 more commandbutton on the search side 1 for modify and 1 for delede.

Thanks

[Zack Barresse]

This is the code I amended. It takes into account the addition of one Command Button which I labeled 'Delete Contact'. The UserForm code is as follows ...

Option Explicit

Private Sub butt_cls_Click()
    Unload Addresses
End Sub

Private Sub butt_ok_Click()
    Dim cel As Range, lastRow As Long, myRow As Long, _
        aciklama As String, dugme, baslik
    lastRow = Range("A65536").End(xlUp).Row
    myRow = lastRow + 1 'Row to work on
    For Each cel In Range("B2:B30")
        If cel.Value = txtname.Value Then
            MsgBox "This person's details already exist in your database.", _
                vbInformation, "Duplicate"
            Exit Sub
        End If
    Next cel
    If Range("A2").Value = "" Then
       Range("A2").Value = 1
    Else
        Cells(myRow, 1).Value = lastRow
    End If
    Cells(myRow, 2).Value = txtname.Text
    Cells(myRow, 3).Value = txtaddress.Text
    Cells(myRow, 4).Value = txtphone.Text
    Cells(myRow, 5).Value = txtemail.Text
    aciklama = "Entry is succesful"
    dugme = vbOKOnly + vbInformation + vbDefaultButton1
    baslik = "Registration"
    MsgBox aciklama, dugme, baslik
End Sub

Private Sub butt_clear_Click()
    txtname.Text = ""
    txtaddress = ""
    txtphone = ""
    txtemail = ""
    txtname.SetFocus
End Sub

Private Sub butt_clr2_Click()
    cboname.Text = ""
    txtaddress1 = ""
    txtphone1 = ""
    txtemail1 = ""
    cboname.SetFocus
End Sub

Private Sub cboname_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim myName As Range, nameRow As Long, lastRow As Long
    lastRow = Range("B65536").End(xlUp).Row
    Set myName = Range("B2:B" & lastRow).Find(cboname.Value)
    If Not myName Is Nothing Then
        nameRow = myName.Row
        txtaddress1.Value = Cells(nameRow, 3).Value
        txtphone1.Value = Cells(nameRow, 4).Value
        txtemail1.Value = Cells(nameRow, 5).Value
    End If
End Sub

Private Sub CommandButton1_Click()
    Dim myName As Range, nameRow As Long, lastRow As Long
    If cboname.Value = "" Then
        MsgBox "You haven't choosen a Contact!", vbInformation, "ERROR"
        Exit Sub
    Else
        lastRow = Range("B65536").End(xlUp).Row
        Set myName = Range("B2:B" & lastRow).Find(cboname.Value)
        If Not myName Is Nothing Then
            If MsgBox("Are you sure you want to delete the contact " & _
                cboname.Value & "?", vbQuestion, "Delete " & _
                cboname.Value) = vbNo Then Exit Sub
            nameRow = myName.Row
            Range("B" & nameRow).EntireRow.Delete
        End If
    End If
End Sub

This is a copy/paste effort. The ComboBox code was changed from a Change event to a MouseUp event for simplicity (imo) and non-duplication purposes. Does this help?

[TheGreatest]

Does this help?

Thanks firefytr, delete option is working great

[Zack Barresse]

Good! Glad to be of help.

Btw, did you know that you can mark your own threads as Solved? At the top of this thread, click Thread Tools --> Mark Solved --> Perform Action, that's it!

Take care!

[TheGreatest]

Thanks