Cells as checkboxes?

[Guest]

Hi, I need to either

a) put a checkbox into a cell (which I don't think is possible) or
b) make the cell itself act as a click-able checkbox

The reason is that I want the checkboxes themselves to appear and/or
disappear as a result of a conditional "IF" statement selected elsewhere on
the spreadsheet.

Any ideas?

Thanks

 

[Bob Phillips]

Hi Austin,

Try this technique that I have posted previously 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.

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


'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Biff]

Hi!

Very nice!

Just a little nit pick, though..........

My default file settings are font Arial size 10. Row height 12.75.

When I run this routine the "checkmark" is placed in the cell but the row
height automatically changes to 14.25.

Can you tweak the code so that the row height does not change?

Thanks

Biff

 

[Guest]

=IF(B1=1,"ü","û")

This formula outputs a checkmark if B1=1, otherwise it outputs a fancy x
(similar to the checkmark). You will need to change the font of the cell to
windings. Something similar might work for you.

 

[Biff]

P.S.

If I reduce the font size to say, 8, the row height does not change but then
the "checkmark" is too small.

Biff

 

[Guest]

I'm sorry. Ignore my post. I am way off.

=IF(B1=1,"ü","û")

This formula outputs a checkmark if B1=1, otherwise it outputs a fancy x
(similar to the checkmark). You will need to change the font of the cell to
windings. Something similar might work for you.

 

[Dave Peterson]

Look at what it was before the change, do the change and then set it back:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myHeight As Double
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
myHeight = .EntireRow.RowHeight
.Value = "a"
.Font.Name = "Marlett"
.EntireRow.RowHeight = myHeight
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub

(I changed the font size to 8 (80% of 10) and it didn't look very nice.)

 

[Biff]

That works.

Thanks, Dave!

Biff

Look at what it was before the change, do the change and then set it back:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myHeight As Double
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
myHeight = .EntireRow.RowHeight
.Value = "a"
.Font.Name = "Marlett"
.EntireRow.RowHeight = myHeight
End If
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub

(I changed the font size to 8 (80% of 10) and it didn't look very nice.)

 

[Bob Phillips]

Good point Biff. I'll add that to my archive.

Thanks

Bob

That works.

Thanks, Dave!

Biff

 

[Biff]

Thanks, Bob!

Biff

Good point Biff. I'll add that to my archive.

Thanks

Bob

 

[Biff]

Sorry about hijacking the thread!

To address the OP:

The reason is that I want the checkboxes themselves to appear and/or
disappear as a result of a conditional "IF" statement selected elsewhere on
the spreadsheet.

The only way to do that if you use this "clickable cell" method (other than
more code) is to use conditional formatting to "hide" the checkmark. It
would still be there but would not be seen. Is that something you want to
pursue?

Biff