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

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)


"Austin" wrote in message
...
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

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

"Bob Phillips" wrote in message
...
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)


"Austin" wrote in message
...
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

=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.

"Austin" wrote:

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

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

"Bob Phillips" wrote in message
...
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)


"Austin" wrote in message
...
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

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

"Sloth" wrote:

=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.

"Austin" wrote:

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

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 wrote:

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

"Bob Phillips" wrote in message
...
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)


"Austin" wrote in message
...
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





--

Dave Peterson

That works.

Thanks, Dave!

Biff

"Dave Peterson" wrote in message
...
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 wrote:

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

"Bob Phillips" wrote in message
...
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)


"Austin" wrote in message
...
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





--

Dave Peterson

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

Thanks

Bob


"Biff" wrote in message
...
That works.

Thanks, Dave!

Biff

"Dave Peterson" wrote in message
...
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 wrote:

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

"Bob Phillips" wrote in message
...
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)


"Austin" wrote in message
...
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





--

Dave Peterson

Thanks, Bob!

Biff

"Bob Phillips" wrote in message
...
Good point Biff. I'll add that to my archive.

Thanks

Bob


"Biff" wrote in message
...
That works.

Thanks, Dave!

Biff

"Dave Peterson" wrote in message
...
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 wrote:

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

"Bob Phillips" wrote in message
...
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)


"Austin" wrote in message
...
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





--

Dave Peterson

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

"Biff" wrote in message
...
Thanks, Bob!

Biff

"Bob Phillips" wrote in message
...
Good point Biff. I'll add that to my archive.

Thanks

Bob


"Biff" wrote in message
...
That works.

Thanks, Dave!

Biff

"Dave Peterson" wrote in message
...
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 wrote:

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

"Bob Phillips" wrote in message
...
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)


"Austin" wrote in message
...
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





--

Dave Peterson