tick box, how to set up

[Tiddler]

Hi can anyone explain (if possible) how to set up a tick box?

I would like to left cliick on the mouse in a cell (to put tick in) and then
use this tick in a IF statement on another.

Thnaks again Matt

[Biff]

Hi!

Here's some code by Bob Phillips and tweaked by Dave Peterson that does what
you want:

This procedure let's you click on a cell in a range (defined in the code)
and places a "checkmark" in
that cell.


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



This is sheet code.
Right click the sheet tab and paste into the window that opens.

Then, to use the checkmark in an IF formula you simply need to test the cell
to see if it's empty or not.

Biff

"Tiddler" <[email protected]> wrote in message
news:[email protected]...
> Hi can anyone explain (if possible) how to set up a tick box?
>
> I would like to left cliick on the mouse in a cell (to put tick in) and
> then
> use this tick in a IF statement on another.
>
> Thnaks again Matt

[Biff]

Slight typo:

>This is sheet code.
>Right click the sheet tab and paste into the window that opens.

Should be:

Right click the sheet tab and select View Code. Then paste the code into the
window that opens.

Biff

"Biff" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Here's some code by Bob Phillips and tweaked by Dave Peterson that does
> what you want:
>
> This procedure let's you click on a cell in a range (defined in the code)
> and places a "checkmark" in
> that cell.
>
>
> 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
>
>
>
> This is sheet code.
> Right click the sheet tab and paste into the window that opens.
>
> Then, to use the checkmark in an IF formula you simply need to test the
> cell to see if it's empty or not.
>
> Biff
>
> "Tiddler" <[email protected]> wrote in message
> news:[email protected]...
>> Hi can anyone explain (if possible) how to set up a tick box?
>>
>> I would like to left cliick on the mouse in a cell (to put tick in) and
>> then
>> use this tick in a IF statement on another.
>>
>> Thnaks again Matt
>
>

[Tiddler]

Thanks I think? Which section of code refers to "tick box" location and once
I have pasted into sheet tab do I just close the window or save somehow?

Sorry but I 'm fairly new to excel and probably in way to deep thanks for
your help
Matt

"Biff" wrote:

> Slight typo:
>
> >This is sheet code.
> >Right click the sheet tab and paste into the window that opens.
>
> Should be:
>
> Right click the sheet tab and select View Code. Then paste the code into the
> window that opens.
>
> Biff
>
> "Biff" <[email protected]> wrote in message
> news:[email protected]...
> > Hi!
> >
> > Here's some code by Bob Phillips and tweaked by Dave Peterson that does
> > what you want:
> >
> > This procedure let's you click on a cell in a range (defined in the code)
> > and places a "checkmark" in
> > that cell.
> >
> >
> > 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
> >
> >
> >
> > This is sheet code.
> > Right click the sheet tab and paste into the window that opens.
> >
> > Then, to use the checkmark in an IF formula you simply need to test the
> > cell to see if it's empty or not.
> >
> > Biff
> >
> > "Tiddler" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi can anyone explain (if possible) how to set up a tick box?
> >>
> >> I would like to left cliick on the mouse in a cell (to put tick in) and
> >> then
> >> use this tick in a IF statement on another.
> >>
> >> Thnaks again Matt
> >
> >
>
>
>

[Biff]

>Which section of code refers to "tick box" location

This line:

If Not Intersect(Target, Range("A1:A100")) Is Nothing Then

Just change "A1:A100" to whatever range you want.

>I have pasted into sheet tab do I just close the window or save somehow?

After you paste the code you just close the VBE (click the "X")

Now, when you select a cell in the defined range a checkmark will appear.
Select that cell again and the checkmark will be removed.

To use that in an IF formula you might do something like this (based on the
cell having the checkmark):

=IF(LEN(A1),do_something,do_something_else)

Biff

"Tiddler" <[email protected]> wrote in message
news:[email protected]...
> Thanks I think? Which section of code refers to "tick box" location and
> once
> I have pasted into sheet tab do I just close the window or save somehow?
>
> Sorry but I 'm fairly new to excel and probably in way to deep thanks for
> your help
> Matt
>
> "Biff" wrote:
>
>> Slight typo:
>>
>> >This is sheet code.
>> >Right click the sheet tab and paste into the window that opens.
>>
>> Should be:
>>
>> Right click the sheet tab and select View Code. Then paste the code into
>> the
>> window that opens.
>>
>> Biff
>>
>> "Biff" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hi!
>> >
>> > Here's some code by Bob Phillips and tweaked by Dave Peterson that does
>> > what you want:
>> >
>> > This procedure let's you click on a cell in a range (defined in the
>> > code)
>> > and places a "checkmark" in
>> > that cell.
>> >
>> >
>> > 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
>> >
>> >
>> >
>> > This is sheet code.
>> > Right click the sheet tab and paste into the window that opens.
>> >
>> > Then, to use the checkmark in an IF formula you simply need to test the
>> > cell to see if it's empty or not.
>> >
>> > Biff
>> >
>> > "Tiddler" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi can anyone explain (if possible) how to set up a tick box?
>> >>
>> >> I would like to left cliick on the mouse in a cell (to put tick in)
>> >> and
>> >> then
>> >> use this tick in a IF statement on another.
>> >>
>> >> Thnaks again Matt
>> >
>> >
>>
>>
>>

[Tiddler]

EXCELLENT i GOT IT WORKING IN COLUMN N BUT WOULD LIKE TO NARROW THAT DOWN TO
N11-N14.
CAN NARROW DOWN TO N14 bUT N1-10 CAN STILL BE TICKED
(opps sorry about caps)

Also is it possable to allow only one tick at a time

Thanks So far keep it coming
Matt

"Biff" wrote:

> >Which section of code refers to "tick box" location
>
> This line:
>
> If Not Intersect(Target, Range("A1:A100")) Is Nothing Then
>
> Just change "A1:A100" to whatever range you want.
>
> >I have pasted into sheet tab do I just close the window or save somehow?
>
> After you paste the code you just close the VBE (click the "X")
>
> Now, when you select a cell in the defined range a checkmark will appear.
> Select that cell again and the checkmark will be removed.
>
> To use that in an IF formula you might do something like this (based on the
> cell having the checkmark):
>
> =IF(LEN(A1),do_something,do_something_else)
>
> Biff
>
> "Tiddler" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks I think? Which section of code refers to "tick box" location and
> > once
> > I have pasted into sheet tab do I just close the window or save somehow?
> >
> > Sorry but I 'm fairly new to excel and probably in way to deep thanks for
> > your help
> > Matt
> >
> > "Biff" wrote:
> >
> >> Slight typo:
> >>
> >> >This is sheet code.
> >> >Right click the sheet tab and paste into the window that opens.
> >>
> >> Should be:
> >>
> >> Right click the sheet tab and select View Code. Then paste the code into
> >> the
> >> window that opens.
> >>
> >> Biff
> >>
> >> "Biff" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Hi!
> >> >
> >> > Here's some code by Bob Phillips and tweaked by Dave Peterson that does
> >> > what you want:
> >> >
> >> > This procedure let's you click on a cell in a range (defined in the
> >> > code)
> >> > and places a "checkmark" in
> >> > that cell.
> >> >
> >> >
> >> > 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
> >> >
> >> >
> >> >
> >> > This is sheet code.
> >> > Right click the sheet tab and paste into the window that opens.
> >> >
> >> > Then, to use the checkmark in an IF formula you simply need to test the
> >> > cell to see if it's empty or not.
> >> >
> >> > Biff
> >> >
> >> > "Tiddler" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> Hi can anyone explain (if possible) how to set up a tick box?
> >> >>
> >> >> I would like to left cliick on the mouse in a cell (to put tick in)
> >> >> and
> >> >> then
> >> >> use this tick in a IF statement on another.
> >> >>
> >> >> Thnaks again Matt
> >> >
> >> >
> >>
> >>
> >>
>
>
>