Macro Click to change from false to true

[ed.ayers315]

Hi Folks,

Can not find a post for this; I need a macro to to change a cell value from false to true or from 0 to 1.

I have a number of cells I want to be able to just click or double click the cells and change it back and forth as needed.

I tried the check boxes for this but they do not fill my needs.

I have different sheets with multiple cells I need to do this in.

H61 & I61

H63 & I61

H65 & I61

And so on

Thanks for any help

 

[wigi]

Hello

For example, an event for the worksheet where you want this to work on cells H61 and I61:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    If Not Application.Intersect(Target, Range("H61:I61")) Is Nothing Then
        Target.Value = Not Target.Value
    End If

End Sub

Please extend for different ranges and / or different sheets (this could be a Workbook_SheetBeforeDoubleClick event in the ThisWorkbook section of the workbook's event code).

 

[VoG]

Try like this starting with True or False in the cells. Right click the sheet tab, select View Code and paste in

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Intersect(Target, Range("H61,H63,H65,I61")) Is Nothing Then Exit Sub
Cancel = True
Target.Value = Not Target.Value
End Sub

Modify the range in red to suit. Double click a cell to swap its value.

 

[ed.ayers315]

Hi wigi,

Thanks for the reply. I tried this but it changes the value from 0 to -1 then -1 to 0.

Is there a way to get it to do 1 to 0 then 0 to 1?

 

[p45cal]

try a doubleclick event in the sheet's code module:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("H61:I61,H63:I63,H65:I65,H67:I67,H69:I69,H71:I71")) Is Nothing Then
  Cancel = True
  If VarType(Target.Value) = vbBoolean Then
    Target.Value = Not (Target.Value)
  Else
    Target.Value = IIf(Target.Value = 1, 0, 1)
  End If
End If
End Sub

How far does 'and so on' go?

 

[ed.ayers315]

Thanks Folks,

I had to change the 1, 0, 1 to 1, NULL, 1

It is working the way I need it to, you're great!

Thanks