Use Cell as Checkbox

  • Thread starter Thread starter Beth
  • Start date Start date
B

Beth

When the user clicks in a cell, I would like the cell
contents to toggle between X or blank. In other words, I'd
like to use a cell as a checkbox. How would I code that?
 
Beth,

This is a re-print of a previous post of mine that suggests what you want,
although I use a tick, not X.

===========================================

Here is an alternative approach I have previously suggested

Another way is to use this technique of having a check column, and
monitoring
it with a worksheet selection change event. Add your code as needed.

Rather than use a checkbox, I suggest just using a check column. So if we
assume that the data is in A1:E100 (change to suit), clicking in column A
will do what you want with this code. Add this code to the worksheet module
(right-click on the sheet name tab, select the View option, and then paste
this code in).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
With Target
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
Thank you very much. The user is impressed, and now wants
more! Can I add a condition to one of the IF statements?

The user is now asking for the tick mark functionality to
be available in all of column K, but only where the values
in A and C are blank. Otherwise, they want a vlookup to
pull a value in from elsewhere.

I can handle the if statement, and I can handle the
Vlookup. But I don't know how to put them in the code you
so generously supplied. Is it possible?
 
Hi Beth,

As requested.

I have had to make some assumptions regarding the VLOOKUP, the lookup value
cell, the lookup range, the column offset, and where it requires an exact
match. Either adjust or post back with details

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If .Column = 11 Then 'column J
'test if A and C are blank
If .Offset(0, -10).Value = "" And .Offset(0, -7).Value = "" Then
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
Else
.FormulaR1C1 = "=VLOOKUP(RC[-10],myTable,2,False)"
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Beth,

Found a couple of problems with the last post. Try this version instead.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If .Column = 11 Then 'column J
'test if A and C are blank
If .Offset(0, -10).Value = "" And .Offset(0, -7).Value = "" Then
On Error Resume Next
If .Value <> "a" Then
On Error GoTo sub_exit
.Value = "a"
.Font.Name = "Marlett"
Else
.Value = ""
.Font.Name = "Arial"
End If
Else
.FormulaR1C1 = "=VLOOKUP(RC[-10],myTable,2,False)"
.Font.Name = "Arial"
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob Phillips said:
Hi Beth,

As requested.

I have had to make some assumptions regarding the VLOOKUP, the lookup value
cell, the lookup range, the column offset, and where it requires an exact
match. Either adjust or post back with details

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo sub_exit
With Target
If .Column = 11 Then 'column J
'test if A and C are blank
If .Offset(0, -10).Value = "" And .Offset(0, -7).Value = "" Then
If .Value = "a" Then
.Value = ""
Else
.Value = "a"
.Font.Name = "Marlett"
End If
Else
.FormulaR1C1 = "=VLOOKUP(RC[-10],myTable,2,False)"
End If
End If
End With
sub_exit:
Application.EnableEvents = True
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bob,
Thank you very much. The user is impressed, and now wants
more! Can I add a condition to one of the IF statements?

The user is now asking for the tick mark functionality to
be available in all of column K, but only where the values
in A and C are blank. Otherwise, they want a vlookup to
pull a value in from elsewhere.

I can handle the if statement, and I can handle the
Vlookup. But I don't know how to put them in the code you
so generously supplied. Is it possible?
Click to expand...
Click to expand...
 
Back
Top