Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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...... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange if(***) behaviour? | Excel Discussion (Misc queries) | |||
Strange VBA Behaviour | Excel Programming | |||
Strange behaviour | Excel Worksheet Functions | |||
Strange behaviour in VBA Help | Excel Programming | |||
strange behaviour | Excel Programming |