I greet all

I have problem.

I would like that my code acted as follows:

If user wrote some Text (but only in the odd columns) and if this Text is
incorrect with conditions, display MsgBox and written text becomes
deleted.... but my loop acted to many times....

where is mistake?

Pozdrawiam,

Andrzej



Private Sub Worksheet_Change(ByVal Target As Range)

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

End Sub

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

also take care with this line
x <= "NGD" Or x = "NGN"

may be interpreted as
( ( (x <= "NGD") Or x) = "NGN")

so with this in VBA it's better to write
((x <= "NGD") Or (x = "NGN"))

Thanks

Pozdrawiam,

Andrzej

Uzytkownik "abcd" napisal w wiadomosci
...
also take care with this line
x <= "NGD" Or x = "NGN"

may be interpreted as
( ( (x <= "NGD") Or x) = "NGN")

so with this in VBA it's better to write
((x <= "NGD") Or (x = "NGN"))

No. That line is completely unambiguous. From VBA Help:

Operator Precedence

When expressions contain operators from more than one category,
arithmetic operators are evaluated first, comparison operators are
evaluated next, and logical operators are evaluated last.

So the comparisons will always occur before the Or.

For readability, it may be helpful to use parens, but not for how it's
interpreted by VBA.



In article ,
abcd wrote:

also take care with this line
x <= "NGD" Or x = "NGN"

may be interpreted as
( ( (x <= "NGD") Or x) = "NGN")

so with this in VBA it's better to write
((x <= "NGD") Or (x = "NGN"))

I'm saying in some cases it's better to use it this way
(do you really check the precedence each time ?
for + and * we know
for or and and only few people think about it each time

using () is readable and also sure

say what you want: I had this kind of surprise once ! So the advice is
(I think) still a very good one !

OK.

Personally, I don't check precedence each time because VB's precedence
is both clear and pretty standard, so I don't need to.

I interpreted your post as a warning that the code would be variably
interpreted by the compiler, which is clearly false.

I certainly can't disagree that your advice would be useful for new or
infrequent coders who don't know the language.


In article ,
abcd wrote:

I'm saying in some cases it's better to use it this way
(do you really check the precedence each time ?
for + and * we know
for or and and only few people think about it each time

using () is readable and also sure

say what you want: I had this kind of surprise once ! So the advice is
(I think) still a very good one !