Disabling double clicking in Excel2002

[Bill Hamilton]

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?

 

[Tom Urtis]

To enable and disable doubleclick (notice not really a toggle, 2 separate macros, one tying into a third), place the following code as indicated. No class modules are needed.

''''''''''''''''''''''''''''''''''''''''''''''''''

In a standard VBA module:

'presumably assigned to one button (or some event you specify) to disable double-clicking:
Sub DoubleClickDisable()
Application.OnDoubleClick = "TurnOff"
End Sub

'presumably assigned to another button (or some event you specify) to re-enable double-clicking:
Sub DoubleClickEnable()
Application.OnDoubleClick = ""
End Sub

'Support for the DoubleClickDisable sub:
Sub TurnOff()
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''

In the workbook module:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call DoubleClickEnable
End Sub

 

[Bill Hamilton]

Thank you. I knew all about onDoubleClick but when I first tried it, it didn't work. It seems to now. Must have been doing something wrong somewhere.

However, this method is obviously on Microsoft's pending obsolete list as there is no Help available for it in V2002 (isn't that what Hidden Language Element means?), so being an inquisitive type I would really, really like to know how one is supposed to do an enable following a disable using the new 'improved' methods available in later versions of VBA.

 

[Tom Urtis]

I would really, really like to know how one is supposed to do an enable following a disable using the new 'improved' methods available in later versions of VBA.

Beginning with Excel97, event procedures are placed into workbook or worksheet modules to account for when a workbook is opened, closed activated, deactivated, changed, calculated, etc.

Here's what you said in your first post:

"where I want to disable double-clicking at one point and re-enable it at another (don't ask)".

So, I didn't ask. But you framed your request within the assumption that the workbook is opened, and at some time of your choosing, you want to disable the doubleclick event, and then enable it later, presumably while the workbook is opened.

To have this happen when you open the workbook, and be deactivated when you close the workbook, place the two following procedures into the workbook module. Be advised, because it is an application level event, any workbooks also open in that same instance of Excel will also be affected. Therefore, the following third and fourth procedures will manipulate the doubleclick event for when the subject workbook is activated and deactivated.


Private Sub Workbook_Open()
Run "DoubleClickDisable"
End Sub

Private Sub Workbook_Activate()
Run "DoubleClickDisable"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "DoubleClickEnable"
End Sub

Private Sub Workbook_Deactivate()
Run "DoubleClickEnable"
End Sub


To easily access your workbook module, find the little Excel workbook icon near the upper left corner of your workbook window, usually just to the left of the File menu option. Right click on that icon, left click on View Code, and paste the following procedure into the large white area that is the workbook module. Press Alt+Q to return to the worksheet.

 

[Bill Hamilton]

OK, I guess I asked for it. What's the difference between code in a workbook module and/or in a worksheet module and/or in an ordinary code module module?

Was there something missing at the end of your last post? "paste the following procedure into ... the workbook module" - there didn't seem to be a following procedure.

My original request was indeed for a method of disabling and enabling double-clicking depending purely on specific acitivites the user was doing, and not just at the workbook open/close times. It could be required to alternate its state many times while the workbook is open (but should obviously be set to 'active' on closing it).

 

[Tom Urtis]

That last sentence should have read "preceding procedures" instead of "following procedure". Sorry.

Regarding your questions about modules, look at Excel's Help under keyword "module" for explanations and examples. The differences will become more evident when you see a number of examples posted on this and other Excel boards.

VBA modules are containers for coded macros, functions, and API calls. Generally, macros (Sub routines) and functions (UDFs) are placed in standard regular modules; code that is meant to affect, or is triggered by, sheet level activities (Worksheet_Change for example) is placed in sheet modules; code that is meant to affect, or is triggered by, workbook or application level activities (Workbook_BeforeSave for example) is placed in workbook module. From a beginner's perspective, you can tell which code goes in which module class by the title of the sub routine. Reading the code and the sub arguments with a more experienced eye would give many clues as to differences in the code.

This topic can go on and on with theory, and I have only simplified the explanation here. The best thing to do in my opinion is to dive in and read the questions on boards like this and see the VBA solutions that are meant for which module. As time goes by, it becomes more evident why some code is suggested,and what class of module it would go into. There are also links on this site for recommendations to books, other sites, and ingenious solutions to coding issues. The learning is half the fun, and takes as long as the time and effort one is willing to devote to the study.

 

[Bill Hamilton]

OK, I'll check all that out.

Thank you for your attention and advice.