Thread: While ... Wend
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
abcd[_2_] abcd[_2_] is offline
external usenet poster
 
Posts: 52
Default While ... Wend

I did not read all the code, but it seems there's a first (big)
problem: when you change something with this line:
Target.Value = ""

then, this line change the value of one cell,
so (it's wanted) the CHANGE event is called again !

Because your tests delete some cells, and an empty cell does not change
it anymore the second time, the loop is not infinite. But this kind of
loop may be infinite sometimes.

YOU MUST stop events while changing cells in the change_events macro

Private Sub Worksheet_Change(ByVal Target As Range)
application.enableevents = false ' STOP EVENTS
Dim x As String
Dim k As Integer

x = Target.Value
k = 1
' blokada wpisu niewłaściwej nazwy cechy do zakładki
While k < 254
If Not Application.Intersect(Columns(k), Target) Is Nothing Then

If x <= "NGD" Or x = "NGN" Then
MsgBox " Incorrect data !!! " & Chr(10) & _
Chr(10) & " Try again "
Target.Value = ""
End If
End If

k = k + 2
Wend
application.enableevents = true 'REACTIVATE EVENTS
End Sub