Undismissible Alert
My apologies for not getting back to this in time....I have been busy
with fixing up the problem following up your suggestions together with
some other run-time errors. Now it seems settled down. Well hopefully!
Good spotting, Greg. All your 3 points make a lot of sense!! I omitted
some details that I thought would not matter much for "simplicity"
reason. Apparently it did do as supposed but make my post less
informative, e.g. I didn't put the another existing label, ws_exit,
which contaiins the statement enabling the events. sorry about that.
I found the major mistake I made was placing two labels one after
another, but didn't know it's essential to place "Exit Sub" as the last
clause for EACH label except for the last label(not necessary). I
finally got it working using the follows:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit
'Find the "blank Row"
Dim textRow As Integer
textRow = Range("Blank_row").Row - 1
Application.EnableEvents = False
''check for invalid Pool nomination entries
If (Target.Column = 3 Or Target.Column = 9) And Target.Row 25 And
_
Target.Row <= textRow And Target.Text = "Pool" And
Range("F10").Value < "Pooling" _
Then GoTo wrongPoolEntry
' <other processing, too much to list all....hopefully safe to do
this this time!
ws_exit:
Application.EnableEvents = True //<--It's enabled here
On Error GoTo 0
Exit Sub //<-- was not here before which caused the BIG
problem
wrongPoolEntry:
MsgBox "Invalid entry - 'Pool' does not match Transaction Type
'Daisy Chain'.", vbExclamation
Target.ClearContents
Cells(Target.Row, 2).Select
Application.EnableEvents = True
End Sub
Missing out 'Exit Sub' also leaded to the undismissisble alert problem
since the execution went through label 'wrongPoolEntry' everytime when
the Sub is invoked which is always the case because it's trigger by
worksheet_change event. This explains why it was undismissible.
Again big thanks to you and Vic for your kind help!!
Regards
Frank
|