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 wrote:

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

On Mon, 29 Sep 2008 07:32:08 -0500, Dave Peterson
wrote:

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 wrote:

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

On Sun, 28 Sep 2008 22:32:52 -0500, Dave Peterson
wrote:

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 wrote:

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

--

Dave Peterson