Search and delete record, using macros

[brunces]

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]

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]

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]

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]

mdmackillop,

Thank you very much, it worked pretty fine!

Hugz.

Bruno