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
>

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
<(E-Mail Removed)> 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
> <(E-Mail Removed)> 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 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
<(E-Mail Removed)> 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
>>

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
<(E-Mail Removed)> 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
>> <(E-Mail Removed)> 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
> <(E-Mail Removed)> 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
> >> <(E-Mail Removed)> 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,

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




On Mon, 29 Sep 2008 10:26:52 -0500, Dave Peterson
<(E-Mail Removed)> wrote:

>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
>> <(E-Mail Removed)> 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
>> >> <(E-Mail Removed)> 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

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 wrote:
>
> 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
>
> On Mon, 29 Sep 2008 10:26:52 -0500, Dave Peterson
> <(E-Mail Removed)> wrote:
>
> >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
> >> <(E-Mail Removed)> 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
> >> >> <(E-Mail Removed)> 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