That is the wrong location for the code Mike posted... he posted event code (not a macro), so it needs to be put in the worksheet's code module. Right click the name tab for the sheet that is to have this functionality and click "View Code" from the popup menu that appears... copy paste the code Mike gave you (with the modification I posted below) in the code window that just opened up... that is it, do nothing else code-wise (well, except to delete the code you said you added in the general module you inserted), instead, go to the worksheet and double click a cell in Column B... Mike's code will execute automatically as a result of your double click action.I have put the code in the VBA editor (via insert module),
Mike... I think you might want to assign True to the Cancel argument to stop the double click in Column B from causing the cell to go into Edit Mode.Yes, here is the code
Code:Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) With Target If .Column = 2 Then .Value = Not (CStr(.Value) = "True") If .Value Then .Interior.Color = RGB(0, 255, 0) Else .Interior.Color = RGB(255, 0, 0) End If [B][COLOR="#FF0000"]Cancel = True[/COLOR][/B] End If End With End Sub
Note that both the Value and the color change. Making sorting and downstream calculations easier.
Thank you for your clear explanation. I have put the code in the VBA editor (via insert module), after which I am asked for a name. I have given the name TOGGLE. After that the following is displayed:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .Column = 2 Then
.Value = Not (CStr(.Value) = "True")
If .Value Then
.Interior.Color = RGB(0, 255, 0)
Else
.Interior.Color = RGB(255, 0, 0)
End If
End If
End With
End Sub
Sub Toggle()
End Sub
After I have pressed F5 to run the macro, it seems nothing is happening, when I doubleclick on a cell in column B nothing happens. After that I tried to copy the code and place it between Sub Toggle () and End Sub:
Sub Toggle()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
With Target
If .Column = 2 Then
.Value = Not (CStr(.Value) = "True")
If .Value Then
.Interior.Color = RGB(0, 255, 0)
Else
.Interior.Color = RGB(255, 0, 0)
End If
End If
End With
End Sub
End Sub
The result is an error message box stating Compile error: Expected End Sub.
What basic thing am I doing wrong here?
Bertha
Mike... I think you might want to assign True to the Cancel argument to stop the double click in Column B from causing the cell to go into Edit Mode.
.Value = Not(CStr(.Value) = CStr(True))