Double click VBA 'checklist' function but just ONE tick per row

[bakarken]

Hi all

ive found a vba somewhere online where double clicking in a cell causes a tick to appear on any cell within a range (in my case I5:M32), but as I would use this VBA on a checklist, I would only want excel to allow one tick per row. If someone ticked I5 and then J5, the original tick in I5 would disappear as it was replaced by the new one on the same row. Make sense?


very grateful

 

[MrIfOnly]

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("I5:M32")) Is Nothing Then
    If Target.Value = "X" Then
        Target.Value = ""
    Else
        Range("I" & Target.Row & ":M" & Target.Row).ClearContents
        Target.Value = "X"
    End If
    Target.Offset(1).Select
End If
End Sub

Regards,

CJ

 

[bakarken]

Amazing thank you so much!

 

[MrIfOnly]

You are very welcome!

 

[bakarken]

CJ might you also know how to combine this macro with another I want to achieve?

let's say I want the exact VBA that you provided with me, except that if the user wishes to select, say, I6, the rows 7:10 will hide themselves?

The I column in my checklist is like a 'questions not applicable' section, would you know how to do this?

Thanks and no worries if not

 

[MrIfOnly]

This should do it:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("I5:M32")) Is Nothing Then
    If Target.Value = "X" Then
        Target.Value = ""
    Else
        Range("I" & Target.Row & ":M" & Target.Row).ClearContents
        Target.Value = "X"
    End If
    Target.Offset(-1).Select
[COLOR=#ff0000]    If Range("I6").Value = "X" Then
        Rows("7:10").EntireRow.Hidden = True
    Else: Rows("7:10").EntireRow.Hidden = False
    End If[/COLOR]
End If
End Sub

You can duplicate the new if-then-else lines (in red) for other cells and hidden rows as you see fit and they should work in concert.

Note that I changed the offset value in the line: Target.Offset(-1).Select from 1 to -1 so that it doesn't select a cell in the hidden rows, but you can change that to select any cell you like or take it out altogether.

Regards,

CJ

 

[bakarken]

Absolutely amazing thank you so much once again!

 

[MrIfOnly]

You are very welcome again!