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

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



"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

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


On Sun, 28 Sep 2008 19:42:01 -0700, AndyM
wrote:

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



"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

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

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

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

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

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