MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Simple check in VBA for Excel


Posted by Walter DB on September 07, 2000 12:25 AM

I have inserted the following statements in an Excel Sub:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Dim Stringa As String

' Check NAME
If Range("D6").Value = “ “ Then
Range("D6 ").Select
MsgBox "Wrong NAME!"
Exit Sub
End If

End Sub

When I execute, in my Excel sheet, this Sub, and I don't insert any value in the cell "D6", VBA respond to me correctly with the message that I typed (e.g: Wrong NAME!"): the problem is that VBA respond twice with this message every time I don't type the NAME in.
Is it possible to eliminate the second message?


Posted by Ivan Moala on September 07, 0100 1:06 AM

Dim Stringa As String If Range("D6").Value = “ “ Then Range("D6 ").Select MsgBox "Wrong NAME!" Exit Sub End If


When using application events you have to watch for
any recursion i.e. the selection change event is
triggered by a change in selection weather by code
or user interface (keyboard etc.)
In your code you check after a selection change
AND then make a selection change via code.
To get over this you need to disable the event
procedure like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Dim Stringa As String

' Check NAME
If Range("D6").Value = "" Then
Application.EnableEvents = False
Range("D6 ").Select
MsgBox "Wrong NAME!"
Application.EnableEvents = True
Exit Sub
End If

End Sub

HTH

Ivan

Posted by Walter DB on September 07, 0100 1:14 AM

Thanks

Ok, as usual, I thank you very much.

Walter