Cancel Out of UserForm Return Focus

[Minitman]

Greetings,

I am using a Worksheet_BeforeDoubleClick event to go into a UserForm.
When I come out the focus is in the cell I double clicked on.

Is there some way to come out of the UserForm (normally or bug-out)
and not still be in that cell?

Any ideas, thoughts or suggestions would be welcome and appreciated.

-Minitman

 

[AndyM]

At the end of your Worksheet_BeforeDoubleClick event, just add a line to
select a different cell. You just want to make sure you are selecting a
different cell and not the same one that was double clicked. The following
code will select the cell 1 row above the active cell. If active cell is the
first cell, it will select the row below.

If ActiveCell.Row > 1 Then
Cells(ActiveCell.Row - 1, ActiveCell.Column).Select
Else
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
End If

 

[Dave Peterson]

Do you mean that the cell is selected or that you're in edit mode for that cell?

If you meant that you're in edit mode, add:
Cancel = True
to your _beforedoubleclick event code.

 

[Minitman]

Hey Dave,

Thanks for the reply. Your solution works, just wish my code worked
as well!

Cancel = True works the 1st time I double click a cell in column A.
After that, before double clicking stops working altogether

I have two separate columns set up for two different routines
separated by select case statements. They both work the first time.
Neither works after the first use in column A. The routine running
from the column C before double click event, works fine until the
first time I double click on column A. The next time I try to double
click on either column, it doesn't work. I have to close down and
reopen the workbook to reset it.

I don't even know where to begin looking.

Any ideas?

-Minitman

 

[Dave Peterson]

Cancel = True

stops the editing of the cell--it won't stop events from being processed.

If I had to guess, I'd guess you had a line like:
application.enableevents = false
in your code.

And you don't turn it back on before your subroutine ends. Maybe you have an
"exit sub" before the "application.enableevents = true" line.

Maybe you just forgot to turn it back on????

Difficult to really help without seeing the code.

 

[Minitman]

Hey Andy,

Thanks for the reply.

Your solution also works, with the same problem as I mentioned in my
reply to Dave. Triggering this before double click event in column A
seems to turn off the event after the first use. closing the workbook
seems to be the only way to reset that event handler.

I am really at a loss as to what is causing this effect!!!

Anyone have any ideas or thoughts as to what is happening?

Any help would be greatly appreciated.

-Minitman

 

[Minitman]

Hey Dave,

Very good guess!!! Thank you.

That was the problem! I had turned off the enable events in a few of
my subs and forgot to turn it back on in the cancel sub!!!

That is now fixed!

One question, I now turn off enable events at UserForm initialization
and turn it back on at the two code exit points. How can I turn on
enable events if the user clicks on the "X" in the upper right hand
corner, which will bypass my code and leave enable events off?

Any ideas?

-Minitman

 

[Dave Peterson]

First, I would turn off events right before I didn't want them enabled. Then
I'd turn them back on right after I finished--kind of a sandwich.

application.enableevents = false
activesheet.range("a1").clearcontents
application.enableevents = true

Alternatively, you could branch to an exit point and turn it back on there--nice
for error handling...

On error goto ErrHandler:
'something that may cause an error
on error goto 0
'lots more code...

ErrHandler:
application.enableevents = true
end sub

=======
But you have a couple of more choices...

You could put the code in the UserForm_QueryClose event:

Option Explicit
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
application.enableevents = true
End Sub


Or you could call the cancel button (or whatever button the users click on to
close the userform):

Option Explicit
Private Sub CommandButton1_Click()
'cancel button???
Application.EnableEvents = True
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Call CommandButton1_Click
End If
End Sub

I'd do the first version (the sandwich) if at all possible. It makes it much
easier to see where things can break. And I'll know the status in any other
portion of the code.

 

[Minitman]

Hey Dave,

I did some experimenting while I was waiting and found 2 UserForm
events I was not familiar with: Deactivate & Terminate (I didn't see
QueryClose or I would have tried that one as well, which I did after
reading your last reply). I put MsgBoxes in each to identify them,
then I tested them.

Is there any difference between Terminate & QueryClose? I noticed
that QueryClose has arguments and Terminate does not. But I don't
know which is the better event to use. Could you explain the pro's &
con's of each? Thanks

Deactivate never fired but both QueryClose & Terminate did (in that
order). Placing the EnableEvents code in either one of those seems to
work. Thanks.

I really do not know when I need to turn off or turn on the various
error preventing routines. So I have taken the shotgun approach:

-----------------------------------------------------------------------------------
In UserForm_Initialize I started with:

With Application
.EnableEvents = False
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
-----------------------------------------------------------------------------------
In UserForm_QueryClose I finished with:

With Application
.EnableEvents = True
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
-----------------------------------------------------------------------------------

My workbooks were so big I had to do something, this was the first
thing I tried. It worked - sort of.

I have since split the worksheets into individual workbooks (boy is
that a pain to go from formulas in the same workbook to vba across
many workbooks). But it is moving along. This situation is part of
the legacy of that spit (this project is much bigger then I
anticipated. It's going on two years now and I'm not even half-way
there yet. But I am learning quite a bit as I go).

Thanks again for your help, Dave, it is really appreciated.

-Minitman

 

[Dave Peterson]

I'd still use the sandwich method. I'd rather control when and where I turn
off/turn on events.

But the _queryclose event seems like a natural location to me.

 

[Minitman]

Thanks Dave,

I appreciate your taking the time to give such good advise.

Your recommendations are what I really needed to reevaluate and
rewrite this code.

Again, thanks.

-Minitman