Event Code Firing More Than Once

Simon Lloyd · Nov 2nd 2006

Dave, thanks i try with what limited knowledge i have, if you dont mind take a look at the code below, i read your article and added the appropriate line, the code works fine except the msgbox has to be ok'd twice before it exits sub any ideas why?

Code

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim rng As Range
    Dim MyCell
    Set rng = Range("A2:A100")
    If Not Intersect(Target, rng) Is Nothing Then
        With rng
            For Each MyCell In rng
                If MyCell = "" Then
                    MyCell.Select
                    MsgBox "Please use this next blank cell"
                    Exit Sub
                 
                End If
            Next
        End With
    End If
End Sub

Display More

Regards,
Simon

Dave Hawley · Nov 2nd 2006

Re: Restrict Selection Event Code To Range

Simon, please don't posts question in other members Threads. I know you are trying to help the OP but the question doesn't fit the Thread Title. I have moved this to it's own Thread.

Now, the problem is due to the Select Method being used. It fires the Event again. To stop this use

Code

Application.EnableEvent=False
MyCell.Select
Application.EnableEvent=True

Simon Lloyd · Nov 2nd 2006

Re: Event Code Firing More Than Once

Thanks Dave, and apologies!

Regards,
Simon

Event Code Firing More Than Once

Participate now!

Share

Users Viewing This Thread