PDA

View Full Version : [SOLVED] Search and delete record, using macros



brunces
07-14-2005, 01:01 PM
Friends,

Please, if possible, take a look at my "attached file" for you to understand what I want. :)

I need to know how to do 3 things:

1) When clicking on "Add client to DB" button:

Check if the SSN typed in A5 already exists in the data base below, in column A (maybe using VLookup, I don't know).
- If so, this new record can't be added to the data base (a MsgBox will tell me that).
- If not, the record can be added.

2) When clicking on "Delete client from the DB" button:

Check if the SSN typed in A5 already exists in the data base below, in column A (maybe using VLookup, I don't know).
- If so, delete the entire line where that SSN is found.
- If not, a MsgBox will tell me that the SSN doesn't exist.

3) When clickin on any of both buttons...

Excel shows me everything it's doing while macros are running (range, copy, select, paste, etc). Is there any way to tell Excel not to show me those actions during the running macros? So that, when I click any button, Excel does what it has to do without showing me what it's doing.

That's it, fellows. I hope you can help me.

Any doubt, ask me, please. :)

Thank you very much for your attention.

Hugs for all.

Bruno

mdmackillop
07-14-2005, 01:41 PM
In steps - 1

Add the following to your sub after the initial If/ElseIf lines
If Not (Range("A9", Selection.End(xlDown)).Find(What:=[A5].Text, _
MatchCase:=False) Is Nothing) Then
[A5].Select
MsgBox "Code Exists"
Exit Sub
End If


-2


Sub ClientDelete()
Dim ToDelete As Range
Set ToDelete = Range("A9", Selection.End(xlDown)).Find(What:=[A5].Text, MatchCase:=False)
If ToDelete Is Nothing Then
MsgBox "Code not found"
[A5].Select
Exit Sub
Else
ToDelete.EntireRow.Delete
End If
End Sub



-3

Add "Application.ScreenUpdating = False" at the head of your code and "Application.ScreenUpdating = True" at the end.

brunces
07-15-2005, 05:41 AM
mdmackillop,

Thank you very much for your attention and your help. It worked exactly the way I wanted. :)

Just one doubt...

In the macro ClientDelete, I realized that if the cursor is not positioned in any cell within that "range.find" you created, it just doesn't delete the record and returns the MsgBox, just like if the SSN really didn't exist. For example...

- You type an existing SSN in A5 and press Enter.
- The cursor, then, goes down to the cell A6 (of course!).
- Then you click on the button "Delete client from DB".
- It returns the MsgBox, telling the SSN doesn't exist because the cursor is positioned on A6.

If after typing an existing SSN in A5 we put the cursor on any cell within the range "A9:A9.End(xlDown)", the button "Delete client from DB" works perfectly, I mean, the record is deleted.

I don't know if it happens over there, with you as well, but here, it does. To "cheat" the code, I put a Range("A9").Select before it starts the running the rest. What I want to know is... Did I make it right? Or is there any other smoother solution for that? :)

Here's my code!



Sub ClientDelete()
Application.ScreenUpdating = False
Range("A9").Select 'I put this and it worked fine! Is that right?

If Range("A5").Value = "" Then
MsgBox "You haven't typed the SSN yet!"
Range("A5").Select
Exit Sub
Else:
Dim ClientToDelete As Range
Set ClientToDelete = Range("A9", _
Selection.End(xlDown)).Find(What:=Range("A5").Text, _
MatchCase:=False)
If ClientToDelete Is Nothing Then
MsgBox "This client doesn't exist."
Range("A5").Select
Exit Sub
Else:
ClientToDelete.EntireRow.Delete
Range("A5:D5").ClearContents
Range("A1").Select
Range("A5").Select
End If
End If
Application.ScreenUpdating = True
End Sub


Thanks a lot for your attention, mdmackillop. :)

Hugs,

Bruno

mdmackillop
07-15-2005, 03:03 PM
Sorry about that. The problem was the word Selection. Change that code line to


Set ToDelete = Range("A9", Range("A9").End(xlDown)).Find(What:=[A5].Text, MatchCase:=False)


and it does not matter where the active cell is.

Re your comment "- The cursor, then, goes down to the cell A6 (of course!)." Not necessarily, you can change this in Tools/Options/Edit/MoveSelectionAfterEnter.
I also made up a KB Item a while ago for entering text in a restricted set of cells here http://www.vbaexpress.com/kb/getarticle.php?kb_id=347

brunces
07-18-2005, 09:31 AM
mdmackillop,

Thank you very much, it worked pretty fine! :)

Hugz.

Bruno