PDA

View Full Version : Solved: Find-Change-Delete



TheGreatest
11-05-2004, 07:52 AM
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
11-05-2004, 09:20 AM
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
11-05-2004, 10:09 AM
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
11-05-2004, 12:37 PM
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
11-05-2004, 01:11 PM
Does this help?
Thanks firefytr, delete option is working great:hi:

Zack Barresse
11-05-2004, 03:12 PM
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
11-09-2004, 07:24 AM
Thanks