Hello people!
I have a problem with a double click event:
Basically the following code works to start one of two macros (according to
which cell I double click)

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim dcCell As Range

If Target.Value 0 Then
Cancel = True
Else: Exit Sub
End If
If Target.Column = 1 Then
Call POtoContract(dcCell:=Target)
Else
If Target.Column = 4 Then
Call copytableline(cyCell:=Target)

End If
End If
End Sub

Now at one point in the "called" macro the user is asked to input a name, if
he spells the name wrong a message box warns the user and then the sub exits.
After that if I try to double click again it goes into editing and ignores
the double click event macro until I restart the workbook.
Does anyone know what is wrong?
Thanks for any help,
Joe.

Check theposition of your Cancel = True code.

You'll need it after you run your secondary code
--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"goodfish" wrote:

Hello people!
I have a problem with a double click event:
Basically the following code works to start one of two macros (according to
which cell I double click)

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim dcCell As Range

If Target.Value 0 Then
Cancel = True
Else: Exit Sub
End If
If Target.Column = 1 Then
Call POtoContract(dcCell:=Target)
Else
If Target.Column = 4 Then
Call copytableline(cyCell:=Target)

End If
End If
End Sub

Now at one point in the "called" macro the user is asked to input a name, if
he spells the name wrong a message box warns the user and then the sub exits.
After that if I try to double click again it goes into editing and ignores
the double click event macro until I restart the workbook.
Does anyone know what is wrong?
Thanks for any help,
Joe.

Hi Joe,

Your code will always go into edit mode if the cell that is double clicked
is zero because you only cancel the edit mode if the cell is 0. However,
because you say that if you restart excel then it works correctly so I am
assuming that is not your problem. Therefore, perhaps you can share the code
in the called subs so it can be viewed and tested to see what is occurring.

I don't suppose that you have any Application.EnableEvents = False anywhere
in your code and it is exiting the sub before turning events back on because
that will cause your problem.

Also I wonder if there is a reason for inserting the parameters passed to
the called routines the way you have done. You can simply use Target. Plus I
personally think the code is easier to read if you use Select Case in lieu of
multiple If statements like the following example.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)

If Target.Value 0 Then
Cancel = True
Else: Exit Sub
End If

Select Case Target.Column
Case 1
Call POtoContract(Target)
Case 4
Call CopyTableLine(Target)
End Select

End Sub

--
Regards,

OssieMac

I set up a test based on your description of how your code is organized. I
find that if the Exit Sub executes in the called procedure, which indicates
the input data could not process, then the macro completes and leaves the
target cell, which initiated the process, in edit mode. This is normal. If
double click in the same cell, it does nothing because it is in edit mode
for that cell. But, if I click in a different cell in the same column, or
the alternate column (1 or 4), then it will initiate the macro. This is all
normal behavior. You could probably add a line in your called code like:

Target.offset(0, 1).Select

Put it just before the Exit Sub line and it will take the original cell out
of edit mode.

"goodfish" wrote in message
...
Hello people!
I have a problem with a double click event:
Basically the following code works to start one of two macros (according
to
which cell I double click)

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
Dim dcCell As Range

If Target.Value 0 Then
Cancel = True
Else: Exit Sub
End If
If Target.Column = 1 Then
Call POtoContract(dcCell:=Target)
Else
If Target.Column = 4 Then
Call copytableline(cyCell:=Target)

End If
End If
End Sub

Now at one point in the "called" macro the user is asked to input a name,
if
he spells the name wrong a message box warns the user and then the sub
exits.
After that if I try to double click again it goes into editing and ignores
the double click event macro until I restart the workbook.
Does anyone know what is wrong?
Thanks for any help,
Joe.

Hi and thanks for all your suggestions. I had lost track of my post - no
email confirmation and nothing coming up when i ran a search for it in
communities - and I have just found it through my profile.

Anyway I did have an Enable Events = False (silly me) that was the cause for
having to restart excel.
About the Select case.. The reason I use a lot of If statements is that I
don't have sufficient programming knowledge. Now I have used the code
OssieMac posted and it works a treat.
Thanks again


"OssieMac" wrote:

Hi Joe,

Your code will always go into edit mode if the cell that is double clicked
is zero because you only cancel the edit mode if the cell is 0. However,
because you say that if you restart excel then it works correctly so I am
assuming that is not your problem. Therefore, perhaps you can share the code
in the called subs so it can be viewed and tested to see what is occurring.

I don't suppose that you have any Application.EnableEvents = False anywhere
in your code and it is exiting the sub before turning events back on because
that will cause your problem.

Also I wonder if there is a reason for inserting the parameters passed to
the called routines the way you have done. You can simply use Target. Plus I
personally think the code is easier to read if you use Select Case in lieu of
multiple If statements like the following example.

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)

If Target.Value 0 Then
Cancel = True
Else: Exit Sub
End If

Select Case Target.Column
Case 1
Call POtoContract(Target)
Case 4
Call CopyTableLine(Target)
End Select

End Sub

--
Regards,

OssieMac