VBA -- DoubleClick on Cell with Value = 1

Try this instead

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

 If Target.Value = vbNullString Then
    Target.Value = 1
 Else
  Target.Value = vbNullString
 End If
    
Cancel = True
End Sub

Try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Value = 1 Then
    Target.Value = vbNullString
    Else
    Target.Value = 1
End If
Cancel = True
End Sub

EDIT: Crooza types faster than me. Nice work!

This will limit the action to only cells B3:B10

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

If Target.Row >= 3 And Target.Row <= 10 And Target.Column = 2 Then

 If Target.Value = vbNullString Then
    Target.Value = 1
 Else
  Target.Value = vbNullString
 End If
End If
Cancel = True
End Sub

It isn't working becasue it looks like you haven't set the range name for the column.

Go to Formulas -> named ranges and create a new range called 'rangeone' and set it to B4 to B17 and it will work on column 1 only

You can set a range like this

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

Dim rng As Range

Set rng = Range("b3:b10")

If Not Application.Intersect(Target, rng) Is Nothing Then

 If Target.Value = vbNullString Then
    Target.Value = 1
 Else
  Target.Value = vbNullString
 End If
End If
Cancel = True
End Sub

or if you want to used a range already set up do it like this

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

Dim rng As Range

Set rng = Worksheets("sheet1").Range("rangeone")
 

If Not Application.Intersect(Target, rng) Is Nothing Then

 If Target.Value = vbNullString Then
    Target.Value = 1
 Else
  Target.Value = vbNullString
 End If
End If
Cancel = True
End Sub

OK I'm a bit lost now. You can either set the range that it will work in by hardcoding in the macro like I did above like this

Set rng = Range("b3:b10")

or

You can have the flexibility of setting a range in your worksheet say "Rangeone" and then referencing that in your code like this

Set rng = Worksheets("sheet1").Range("rangeone")

so if you want to set a particular column you can either set it in the hardcode like this

Set rng = Range("C:C")

which will be column C

or you can set the give the entire column a named range like "rangeone"

Code like this...

Set rng = Worksheets("sheet1").Range("rangeone")

Only work when I set table called "rangeone" and it applies entire columns.

When I changed Table name into Table, and named single column to "rangeone" I get an error. Just like this image below
Error.JPGAttachment 374380

Perhaps, to make this lot easier, there are attached .xlsm file for you to see it.

Thanks in advance.