Pass value variable to worksheet event

[Leo]

Hello, can anyone help me with the following.

I have a module in vba in which I give a certain boolean the value True. It is a public variable with the name blnStop (Public blnStop as boolean).
If the value is True then the code under the worksheet_change should not work:



Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If blnstop = True Then Exit Sub

If Target.Column = 7 Or Target.colums = 14 Then ......

End Sub

Somehow the value of the boolean blnstop (which is True) is not passed to this event.
In this event the boolean has the value False.

Can anyone help me with this please.
What am I doing wrong?

 

[Harald Staff]

It's not good when you declare public blnStop one place and code reads
blnstop another. Are you copy-pasting code here or retyping it? Make sure
every module has Option Explicit on top, check Require variable declaration
in Preferences, and check all your spelling.

Best wishes Harald

 

[Leo]

Thank you for your answer.
Unfortunately it is not the solution.

The code in which I give the boolean blnStop the value True is in a Form (and not in a module as I mentioned before (sorry, my mistake)).
In that code the value of the boolean blnStop is and stays True (untill theend of that code where the value of the boolean blnStop is False again), while the value of the boolean blnStop in the object of a worksheet is False.. So it seems the value of the boolean blnStop in a Form is not passed to aworksheet_event?

code of the form:
Option Explicit
Public blnStop As Boolean

Sub Invullen()

blnStop = True

.....

blnStop = False

End Sub

code of the worksheet event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If blnStop = True Then Exit Sub

If Target.Column = 7 Or Target.Column = 15 Then LijstRefresh

End Sub


When the code of the form is being processed the changes in columns 7 and 15 should not lead to process LijstRefresh.

Can you please help me?

 

[Harald Staff]

If Userform1.blnStop = True Then Exit Sub

replace Userform1 with whatever your instance of the form is named.

HTH. Best wishes Harald

"Leo" <[email protected]> skrev i melding
Thank you for your answer.
Unfortunately it is not the solution.

The code in which I give the boolean blnStop the value True is in a Form
(and not in a module as I mentioned before (sorry, my mistake)).
In that code the value of the boolean blnStop is and stays True (untill the
end of that code where the value of the boolean blnStop is False again),
while the value of the boolean blnStop in the object of a worksheet is
False. So it seems the value of the boolean blnStop in a Form is not passed
to a worksheet_event?

code of the form:
Option Explicit
Public blnStop As Boolean

Sub Invullen()

blnStop = True

.....

blnStop = False

End Sub

code of the worksheet event:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If blnStop = True Then Exit Sub

If Target.Column = 7 Or Target.Column = 15 Then LijstRefresh

End Sub


When the code of the form is being processed the changes in columns 7 and 15
should not lead to process LijstRefresh.

Can you please help me?