Undismissible Alert

[OrientalPearl]

Hello,

Basically I want the following to happen:
Whenever a user changes some value(kinda focus change?), macro will
check for the validality of the entry. In case of an invalid one, it
clears the bad content and fires an alert, waiting for a new input from
the user.

here's the code I use:
==========================================
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.Value = "Pool" And Range("F10").Value
<> "Pooling" Then GoTo wrongPoolEntry

wrongPoolEntry:
MsgBox "Invalid entry. Select 'Pooling' as Transaction Type to
proceed.", vbExclamation
Target.ClearContents
Target.Select
Exit Sub
End Sub
==================================

Mainly two undesirable phenomena:
1. Whatever changes to the existing value regardless of valid or
invalid ones, the alert is fired. This should not happen as this should
only if the new value is "Pool" which is taken as invalid whilst the
other cell(F10) is not "Pooling"
2. The alert is undismissible. It comes back immediately everytime when
the OK button is clicked. So nothing else can be done.

What causes the abnormity? Any solution ideas?

Thanks in advance and regards
Frank

 

[OrientalPearl]

Some more weird things are happening:
1. VBA editor does not display any code for any spreadsheet currently
open in Excel. (In Excel, Tools > Macro > Visual Basic Editor. It does
open the editor but nothing in it to work with, cannot even see
anything in the Properties pane)
2. It seems any change to ANY cell on the spreadsheet will trigger the
alert. But the If condition clearly specified the locations being
watched: (Target.Column = 3 Or Target.Column = 9) And Target.Row > 25
And Target.Row <= textRow
3. When I stepped through the code in debugging mode, the execution
went through the GoTo statement at all times, i.e. wrongPoolEntry,
regardless of whether the If conditions are met

I think 2 and 3 are somehow related

 

[OrientalPearl]

Oh another point I forgot to mention which might be useful in
diagnosis.
Since the alert cannot be dimissed during the testing, I use Ctrl +
break to stop the macro from running. That's the only way that I know
may terminate the macro execution. Did this lead to the weird thing 1
mentioned in my last post?

 

[Vic Eldridge]

When I stepped through the code in debugging mode, the execution went

through the GoTo statement at all times

Because there's nothing there telling it not to. Try placing "Exit Sub"
after your If statement.

Regards,
Vic Eldridge

 

[Greg Wilson]

I strongly suspect that your actual code differs from the code that you
posted. Reasons:

1. You disable events and don't reenable events. Therefore, the code
shouldn't run after the first execution.
2. You list the label "ws_exit" but don't have such a label. This should
invoke an error message.
3. The symptom of a undismissable message box implies that the change event
is in a nonterminating loop. This can't happen with the code you posted.

An obvious error is that your "Exit Sub" should proceed the "wrongPoolEntry"
label. I get the impression that you think that code that follows a label
(word followed by a colon) is shielded from execution. This is not the case.
In your code, everything that follows the "wrongPoolEntry:" line will
necessarily execute (unless there is an error or events are disabled). The
"Exit Sub" line should proceed the label so that the code will only there if
it is told to 'jump' to that line with the GoTo statement. What's the point
of having the Exit Sub statement at the end of the procedure when it is over
anyway?

I advise a repost with a verbatim copy of the code as well as provide the
definition of the named range "Blank_row". Is this a dynamic named range?

Regards,
Greg

 

[OrientalPearl]

Hi Greg and Vic,

Thanks to you both for the well needed help, which enables me to
continue working on it at present! I will advise details on any
progress/further obstacles shortly. Thanks for keeping an eye on this
thread.

Kind regards
Frank

 

[OrientalPearl]

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

 

[OrientalPearl]

You're right Vic. It was missing out 'Exit Sub' that caused the
problem(details on my other post).

Thanks and regards
Frank

 

[OrientalPearl]

Sorry for forgetting about the 'blank_row'. Yeah it's dynamic...it's
set right beneath the last row within the user working area. The user
can interact with the area by adding row and delete row(only within
that area). sometimes need to find out how many rows still left....say
the user should not be able to delete a row which is the only row. In
this case, "Blank_row".Row = first_row.Row. Also can loop through from
first_row.Row to "Blank_Row".Row - 1 to process user data.

Regards
Frank