Bill Hamilton
Board Regular
- Joined
- Mar 30, 2004
- Messages
- 95
I'm writing an application in Excel 2002 (V10) where I want to disable double-clicking at one point and re-enable it at another (don't ask). The VBA Help gives the exact example for disabling it, but but gives no clues about how to re-enable it once that's happened.
It needs an event handler in a Class Module thus (as per VBA Help):
Private Sub SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
End Sub
which is entered every time the user double-clicks. What code will re-enable it? Setting Cancel=False will do it, but the problem is to get it executed as there obviously can only be one version of this macro. I've tried setting up a global boolean bD_Click_On_or_Off to set to True or False as required, and changed the Cancel=True above to Cancel=bD_Click_On_or_Off. It goes through the code all right but double-clicking remains on regardless of the setting.
Older versions of Excel could do this no problem. They had sheet.OnDoubleClick="UserSubroutine" which sends execution off to the routine specified when the user double clicks. This is just a null macro if it is to be disabled. It's reinstated to default behaviour by sheet.OnDoubleClick="". This method fails with Excel 2002 as OnDoubleClick does not seem to be available.
Can anyone help with the 2002 way of doing this? Is the answer to enable and disable the event handler at the relevant points in my code? If so, how 's that done?
It needs an event handler in a Class Module thus (as per VBA Help):
Private Sub SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
End Sub
which is entered every time the user double-clicks. What code will re-enable it? Setting Cancel=False will do it, but the problem is to get it executed as there obviously can only be one version of this macro. I've tried setting up a global boolean bD_Click_On_or_Off to set to True or False as required, and changed the Cancel=True above to Cancel=bD_Click_On_or_Off. It goes through the code all right but double-clicking remains on regardless of the setting.
Older versions of Excel could do this no problem. They had sheet.OnDoubleClick="UserSubroutine" which sends execution off to the routine specified when the user double clicks. This is just a null macro if it is to be disabled. It's reinstated to default behaviour by sheet.OnDoubleClick="". This method fails with Excel 2002 as OnDoubleClick does not seem to be available.
Can anyone help with the 2002 way of doing this? Is the answer to enable and disable the event handler at the relevant points in my code? If so, how 's that done?