Calling a UserForm Exit sub

[Bear]

I have some code I'd like to execute when a command button is clicked. The
code is in the exit subroutine of a text box.

Private Sub txtColumnWidth_Exit(ByVal Cancel As MSForms.ReturnBoolean)

But when I say

Call txtColumnWidth_Exit

the compiler insists that I provide the Cancel argument. But I can't figure
out how to do that. None of these work, giving a type mismatch error on
compile.

Call txtColumnWidth_Exit(Cancel:=True)
Call txtColumnWidth_Exit(Cancel:=1)

What type is MSForms.ReturnBoolean and how do I pass it?

Bear

 

[Jay Freedman]

Hi Bear,

The point is moot, because _you_ shouldn't be calling the Exit
routine. It's an event handler, so VBA calls it automatically when the
event occurs (the textbox loses focus), and it takes care of
(invisibly) declaring the Cancel argument and using whatever value it
has when the handler returns.

Really, though, if you want "some code I'd like to execute when a
command button is clicked", then that code should be in the button's
_Click() procedure, not in the text box's _Exit() procedure. You can't
be sure that exiting the text box means that the button has been
clicked... maybe the user clicked a blank area of the userform (and
yes, there's an event handler for that, too).

 

[Bear]

Jay:

I'm familiar with the event handling you're describing, but I was looking
for a way to reuse the code already built into that exit routine. Perhaps
it's sloppy coding, but I've gotten used to simply calling existing event
handler code in some cases. For example, my code for handling a click on the
Close button in the title bar often calls the click event for my Cancel
button.

Perhaps the ideal approach would be to put the code in a separate sub and
call that from the text box exit event and the command button click event
subs. But I was hoping to avoid having to do that.

Bear

 

[fumei via OfficeKB.com]

In general, yes, it is kind of sloppy coding. Jay's point is a "best
practice" concept. Procedures should be explicit. If a control
(commandbutton...whatever) performs actions, they should be explicit to that
control. Yes, yes, they may - MAY - be the same as another procedure. The
point, however, is that a commandbutton click is NOT the same as an exit
event. True, what it does may (or may not) be the same, but it is NOT an
Exit event. So calling the exit event itself is not - generally - a good
practice.

Calling other procedures is fine, and IS a good practice. Calling other
events - that are NOT ACTUALLY HAPPENING - is not.

I have to wonder why you even need to do this. Calling the Exit event for a
textbox you are not actually exiting seems....odd.