PDA

View Full Version : BeforeDoubleClick compatibility Excel 2010



c408
10-07-2011, 12:44 PM
I'm trying to modify an example found on VBAX (titled CheckBox Alternative - I'd post a link but don't have enough posts yet) for a checkbox alternative. I'm using Excel 2010 in Win7 and cannot get the "check/uncheck box" function to work. I've opened the workbook directly from the zip file, the code is in the correct private worksheet module, and the security controls are set to allow the code to run.

Several things complicate this: 1) I've tried running it on colleague's computers and it work flawlessly on Excel 2007 (WinXP) and Excel 2003 (WinXP). 2) I don't think it's a security issue because some of the functionality in the "Mutually Exclusive" examples works fine. 3) I don't think this is an issue with the specific code, I've tried with other examples found online and none of them work.

The issue seems to be with the BeforeDoubleClick part of the code. Double-clicking on the cells within the range just open up the cell for editing (this happnes on both tabs incidentally). Does anyone have any thoughts on this? I've gone as far as I can with my limited knowledge and what google can tell me. Any help is appreciated. Thanks. (the basic check/uncheck cells code is below)

'Code for Worksheet "Cells as Checkboxes"

Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("myChecks")) Is Nothing Then Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
Exit Sub
End If
End Sub

Kenneth Hobs
10-07-2011, 01:18 PM
Welcome to the forum!

Please use VBA code tags for code. Click the VBA icon and paste code between the tags.

For that to work, you need to assign the range and insure that the font exists.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
If Intersect(Target, Range("myChecks")) Is Nothing Then Exit Sub
'set Target font tp "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets Target Value = ""
Cancel = True
End If
End Sub

c408
10-11-2011, 06:29 AM
Thanks - and will do on the VBA tags.

I checked and the Marlett font does exist in my version of excel. I also tried changing the font name to another one and it made no difference. When you say "assign the range", are you referring to "MyChecks"? That is a named range in the example worksheet. I tried defining the range in the VBA code as well, but that made no difference.

Since this example workbook and code work perfectly in 2 other versions of excel, the only thing I can think is that it's a compatibility issue. And since doubleclicking in the cell in the target range doesn't seem to initiate the macro, it just opens the cell for editing, I can only think it has to do with excel10 somehow not recognizing the BeforeDoubleClick as it is written.

Aflatoon
10-11-2011, 08:11 AM
The code is fine in 2010 as written in my testing. Are you saying that if you put a breakpoint on the 'If Target.Count...' line, it never gets hit, but other events do run?

c408
10-11-2011, 08:28 AM
That's correct. That breakpoint isn't hit. The other events that do run are actually in the second sheet included in the workbook that are Worksheet_Change events, not BeforeDoubleClick. The code for the second sheet is below. The Worksheet_Change section works correctly, the BeforeDoubleClick events never run.



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

'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
' If Intersect(Target, Range("Ckboxes")) Is Nothing Then Exit Sub
'Set Target font to "marlett"
Target.Font.Name = "marlett"
'Check value of target
If Target.Value <> "a" Then
Target.Value = "a" 'Sets target Value = "a"
Cancel = True
Exit Sub
End If
If Target.Value = "a" Then
Target.ClearContents 'Sets target Value = ""
Cancel = True
Exit Sub
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Limit Target count to 1
If Target.Count > 1 Then Exit Sub
'Isolate Target to a specific range
' If Intersect(Target, Range("Ckboxes")) Is Nothing Then Exit Sub
'Select a specific subset of the range "Ckboxes"
Select Case Target.Address
Case Is = "$D$2", "$D$4", "$D$6"
'Clear Contents of cells that are not the target
If Target.Address = "$D$2" Then [D4,D6].ClearContents
If Target.Address = "$D$4" Then [D2,D6].ClearContents
If Target.Address = "$D$6" Then [D2,D4].ClearContents
'Place the address of the "checked" cell in "$D$11"
Range("$D$11").Value = Target.Address
Case Is = "$H$3", "$H$5"
'Clear Contents of cells that are not the target
If Target.Address = "$H$3" Then [H5,H5].ClearContents
If Target.Address = "$H$5" Then [H3,H3].ClearContents
'Place the address of the "checked" cell in "$H$11"
Range("$H$11").Value = Target.Address
Case Else
'Populate the cell to the right of Target with its status
If Target.Value = "a" Then
Target.Offset(0, 1) = "Checked"
Else:
Target.Offset(0, 1).Value = "Not Checked"
End If
End Select
End Sub

Aflatoon
10-11-2011, 08:36 AM
Can you post an actual workbook showing the problem for us to test? (no data necessary) Also, do you have Office 2010 SP1 installed?

c408
10-11-2011, 08:47 AM
Yes, service pack 1 is installed. I've attached (I'm pretty sure anyway) the workbook. It's just an example from the KB section of this site.

Thanks for your help.

Aflatoon
10-11-2011, 09:01 AM
Interesting - that works perfectly for me, and I cannot think of any settings that would prevent it (while leaving the Change event functioning). Do you have any addins or startup files loaded? If you open Excel in Safe Mode, then open the workbook, does it work correctly?

c408
10-11-2011, 09:14 AM
No add-ins or start-up files that I'm aware of. Which I'm thinking I must be wrong about since it does, in fact, work in safe mode.

Excel in normal mode has no active application add-ins. How do I check for start-up files?

Aflatoon
10-12-2011, 03:58 AM
Check both of your XLSTART folders and also any folder specified in Excel Options as an additional startup folder.

c408
10-12-2011, 06:33 AM
That did it. I removed the PERSONAL.XLSB file from XLSTART and now the BeforeDoubleClick events are working perfectly. Figuring out why the 2 macros stored in the PERSONAL file interfered is another matter, but that can wait.

Thanks for your help, I'd never have gotten this worked out on my own.

Aflatoon
10-12-2011, 06:53 AM
Glad to be of service. :)

c408
10-13-2011, 06:56 AM
Well this is interesting. I mostly finished up the project yesterday, saved it, went home. Came back in this morning and after working on a few other things switched back to my project workbook and was fiddling with some of the formats. Then I noticed that double-clicking wasn't triggering the code again. Exact same problem as before.

I checked; no add-ins enabled, nothing in the XLSTART folder. Opened the sheet in safe-mode Excel and it works perfectly.

The only thing I can think is that earlier this morning I opened another instance of excel and ran 2 of them for a while. I've closed it since, but that hasn't fixed the problem. Could that have caused a workbook or application reference problem?

Aflatoon
10-13-2011, 07:00 AM
A separate instance should not interfere in any way, assuming it did not start a program/addin that monitors events globally.

GTO
10-13-2011, 07:18 AM
Hi All,

Probably nothing, but 'cat-killin' curiosity'...

What shows in the the project window when not workning (vs. when is working)?

Mark

Kenneth Hobs
10-13-2011, 07:19 AM
If you had a macro that disables events, and an error occurs, that would cause it. Typing this in the VBE Immediate Window and pressing Enter or running it will reenable events.

Application.EnableEvents = True

c408
10-13-2011, 07:35 AM
GTO - They look the same to me. I'm not sure what I'd be looking for though, so that's not a very confident answer.

KH - That's it! I had another macro that turned events and screen updating off, when I added a line to turn events back on at the end of that sub and ran it again, the doubleclick events started working again in my workbook. Lesson learned.

Thank you all for your input, much appreciated.