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
