Worksheet_Change Event Strange Behaviour
 

Hi,

I have been cracking my head to solve this problem for few days to no avail.
hopefully someone can help me.
I have 1 cell with drop down list, when the user enter a value that is not
in the list, they'll get an error (set from Data Validation). If they enter a
correct value, the message will be displayed from Worksheet_Change event
below.

It's all fine if they always enter the correct value but when they enter the
wrong value and correct it afterwards (Excel will ask if they want to try
again), the Worksheet_Change event will run twice. When the user enter the
wrong value twice, the event will run 4 times (it doubles the number).


Private Sub Worksheet_Change(ByVal Target As Range)
Dim c
Dim x
Dim rng As Range
Dim vRow As Double
Dim sPrn As String
Dim currCell As Range
'Dim OldVal
Dim lngFound

On Error GoTo errWorksheet_Change
With Target
If .Count = 1 Then
'Change printer
If Not Intersect(.Cells, Range("C5")) Is Nothing Then
Application.EnableEvents = False
If .Cells < "" Then
Application.ActivePrinter =
Worksheets("Main").Cells(rowPrinter, 3).Value & " on LPT1:"
MsgBox Application.ActivePrinter & " is activated!"
Else
MsgBox "No barcode printer is available!"
End If
Application.EnableEvents = True
End If
continue......

Worksheet_Change Event Strange Behaviour
 

Hi
is Application.EnableEvents = True in the right place? Is the code
below it firing the change event?
regards
Paul

On Feb 27, 10:06*am, Sugih wrote:
Hi,

I have been cracking my head to solve this problem for few days to no avail.
hopefully someone can help me.
I have 1 cell with drop down list, when the user enter a value that is not
in the list, they'll get an error (set from Data Validation). If they enter a
correct value, the message will be displayed from Worksheet_Change event
below.

It's all fine if they always enter the correct value but when they enter the
wrong value and correct it afterwards *(Excel will ask if they want to try
again), the Worksheet_Change event will run twice. When the user enter the
wrong value twice, the event will run 4 times (it doubles the number).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c
Dim x
Dim rng As Range
Dim vRow As Double
Dim sPrn As String
Dim currCell As Range
'Dim OldVal
Dim lngFound

On Error GoTo errWorksheet_Change
With Target
* * If .Count = 1 Then
* * * * 'Change printer
* * * * If Not Intersect(.Cells, Range("C5")) Is Nothing Then
* * * * * * Application.EnableEvents = False
* * * * * * If .Cells < "" Then
* * * * * * * * Application.ActivePrinter =
Worksheets("Main").Cells(rowPrinter, 3).Value & " on LPT1:"
* * * * * * * * MsgBox Application.ActivePrinter & " is activated!"
* * * * * * Else
* * * * * * * * MsgBox "No barcode printer is available!"
* * * * * * End If
* * * * * * Application.EnableEvents = True
* * * * End If
continue......

Worksheet_Change Event Strange Behaviour
 

Hi Paul,

Thanks for responding to my post.
The code below it will be for different cells, I don't want to post it
because it would be too long but basically is another set of below statement.

If Not Intersect(.Cells, Range(xxx)) Is Nothing Then
End If

At the end of the sub there will be this code.


End If 'to close If Count = 1 statement
End With
Exit Sub

errWorksheet_Change:
Application.EnableEvents = True
MsgBox "An error has occured, please contact your system
administrator.", , "Error."

End Sub
Thanks,
Sugih

" wrote:

Hi
is Application.EnableEvents = True in the right place? Is the code
below it firing the change event?
regards
Paul

On Feb 27, 10:06 am, Sugih wrote:
Hi,

I have been cracking my head to solve this problem for few days to no avail.
hopefully someone can help me.
I have 1 cell with drop down list, when the user enter a value that is not
in the list, they'll get an error (set from Data Validation). If they enter a
correct value, the message will be displayed from Worksheet_Change event
below.

It's all fine if they always enter the correct value but when they enter the
wrong value and correct it afterwards (Excel will ask if they want to try
again), the Worksheet_Change event will run twice. When the user enter the
wrong value twice, the event will run 4 times (it doubles the number).

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c
Dim x
Dim rng As Range
Dim vRow As Double
Dim sPrn As String
Dim currCell As Range
'Dim OldVal
Dim lngFound

On Error GoTo errWorksheet_Change
With Target
If .Count = 1 Then
'Change printer
If Not Intersect(.Cells, Range("C5")) Is Nothing Then
Application.EnableEvents = False
If .Cells < "" Then
Application.ActivePrinter =
Worksheets("Main").Cells(rowPrinter, 3).Value & " on LPT1:"
MsgBox Application.ActivePrinter & " is activated!"
Else
MsgBox "No barcode printer is available!"
End If
Application.EnableEvents = True
End If
continue......