VBA BeforeDoubleClick

[Sweedler]

Hello,

I have a column that consists of either "c" (webding empty box) or "a" (webding checkmark). If the cell contains a "c", which is the default, I want a double click to turn it into an "a", but if there is an "a" then I want it to become a "c" again.

Currently trying to repurpose this, but I am not making any headway. It turns it into an "a" just fine, but that is all that it can do.

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

'~~> Check if the double click happened in Range A1:A10
'~~> Also notice the placement of `Cancel = True`
If Not Intersect(Target, Range("AE12:AE206")) Is Nothing Then
'~~> Check if the cell is empty
If Len(Trim(Target)) = 0 Then
Target.Value = "a"
Cancel = True
'~~> Check if the cell already has X. This is to ensure that
'~~> the cell is not cleared if the user has typed something
'~~> else in the cell.
ElseIf UCase(Trim(Target)) = "c" Then
Target.ClearContents
Cancel = True
End If
End If
End Sub

Cheers

 

[Alex Blakenburg]

If you replace your code with this does it do what you want ?

If Not Intersect(Target, Range("A12:A206")) Is Nothing Then
    If Target.Value = "a" Then
        Target.Value = "c"
    ElseIf Target.Value = "c" Then
        Target.Value = "a"
    End If
    Cancel = True
End If

 

[Sweedler]

If you replace your code with this does it do what you want ?

If Not Intersect(Target, Range("A12:A206")) Is Nothing Then
    If Target.Value = "a" Then
        Target.Value = "c"
    ElseIf Target.Value = "c" Then
        Target.Value = "a"
    End If
    Cancel = True
End If

I entered it like this:

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

If Not Intersect(Target, Range("A12:A206")) Is Nothing Then
If Target.Value = "a" Then
Target.Value = "c"
ElseIf Target.Value = "c" Then
Target.Value = "a"
End If
Cancel = True
End If

End Sub



But nothing is happening. The cell simply stays as it is. What am I doing wrong?

 

[RoryA]

Alex's version will only toggle between the tick and the square - it won't do anything to a blank cell, if that's what you are double-clicking?

 

[Alex Blakenburg]

As Rory pointed out there are a number of other possibilities that I didn't know what to do with eg

If the cell contains a "c", which is the default,

Has c as the default yet your code

If Len(Trim(Target)) = 0 Then
           Target.Value = "a"

Effectively makes "a" the default.

What do you want to happen if the cell is blank ?
What do you want to happen if the cell is: not blank, a, or c ?
You are using UCase and Trim do you need that, are additional spaces likely ? Note: your code has UCase it should be LCase

So to flesh it out a bit more and assume if blank it should be "c"

If Not Intersect(Target, Range("A12:A206")) Is Nothing Then
    If LCase(Target.Value) = "a" Or Target.Value = "" Then
        Target.Value = "c"
    ElseIf LCase(Target.Value) = "c" Then
        Target.Value = "a"
    Else
        ' what has to happen if none are true
    End If
    Cancel = True
End If

 

[Sweedler]

Hello

The script works just fine. It was my error here in transferring it. The range I am working with is AE12:AE206, not A12:A206. Updated the script and now it works just fine.

Thank you for your help.

 

[Alex Blakenburg]

Sorry for testing I used column A and meant to change it back to AE when I posted it. Glad you spotted it and got it to work.