Is there a Macro to enable a check box based on the content of a c

I want to be able to have a check appear in the box if I have an amount >0.

What changes the amount - someone editing the cell with the amount?


right click on the sheet tab and select view code.

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.Address = "$B$9" then
If Target.Value >0 then
me.checkbox1.Value = True
else
me.checkbox1.Value = False
end if
End if
end sub

--
Regards,
Tom Ogilvy




"Nausett" wrote:

> I want to be able to have a check appear in the box if I have an amount >0.

How do I get Excel the Checkbox to recognize the new Macro?

"Tom Ogilvy" wrote:

> What changes the amount - someone editing the cell with the amount?
>
>
> right click on the sheet tab and select view code.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> if Target.Address = "$B$9" then
> If Target.Value >0 then
> me.checkbox1.Value = True
> else
> me.checkbox1.Value = False
> end if
> End if
> end sub
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Nausett" wrote:
>
> > I want to be able to have a check appear in the box if I have an amount >0.

The checkbox is a passive victim. Given the assumptions I made, if you have
a checkbox named checkbox1 (from the control toolbox toolbar) and it is
located on the sheet with the code, and you have a cell B9, and with the code
in the sheet module as I described, you change the value of B9 to 3 (as an
example), checkbox1 should get checked.

--
Regards,
Tom Ogilvy


"Nausett" wrote:

> How do I get Excel the Checkbox to recognize the new Macro?
>
> "Tom Ogilvy" wrote:
>
> > What changes the amount - someone editing the cell with the amount?
> >
> >
> > right click on the sheet tab and select view code.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > if Target.Address = "$B$9" then
> > If Target.Value >0 then
> > me.checkbox1.Value = True
> > else
> > me.checkbox1.Value = False
> > end if
> > End if
> > end sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "Nausett" wrote:
> >
> > > I want to be able to have a check appear in the box if I have an amount >0.

I'm getting a Compile Error: Method or Data Member Not Found. Am I doing
something wrong?
Thanks,

"Tom Ogilvy" wrote:

> The checkbox is a passive victim. Given the assumptions I made, if you have
> a checkbox named checkbox1 (from the control toolbox toolbar) and it is
> located on the sheet with the code, and you have a cell B9, and with the code
> in the sheet module as I described, you change the value of B9 to 3 (as an
> example), checkbox1 should get checked.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nausett" wrote:
>
> > How do I get Excel the Checkbox to recognize the new Macro?
> >
> > "Tom Ogilvy" wrote:
> >
> > > What changes the amount - someone editing the cell with the amount?
> > >
> > >
> > > right click on the sheet tab and select view code.
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > if Target.Address = "$B$9" then
> > > If Target.Value >0 then
> > > me.checkbox1.Value = True
> > > else
> > > me.checkbox1.Value = False
> > > end if
> > > End if
> > > end sub
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "Nausett" wrote:
> > >
> > > > I want to be able to have a check appear in the box if I have an amount >0.

Highly likely that you are doing something wrong. I put a checkbox on my
worksheet and named it Checkbox1. I then pasted in the code from the email
into the sheet module for that worksheet.

I entered a 3 in B9, the checkbox was checked.

I entered a 0 in B9,. the checkbox was unchecked.

--
Regards,
Tom Ogilvy

"Nausett" <[email protected]> wrote in message
news:[email protected]...
> I'm getting a Compile Error: Method or Data Member Not Found. Am I doing
> something wrong?
> Thanks,
>
> "Tom Ogilvy" wrote:
>
> > The checkbox is a passive victim. Given the assumptions I made, if you
have
> > a checkbox named checkbox1 (from the control toolbox toolbar) and it is
> > located on the sheet with the code, and you have a cell B9, and with the
code
> > in the sheet module as I described, you change the value of B9 to 3 (as
an
> > example), checkbox1 should get checked.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Nausett" wrote:
> >
> > > How do I get Excel the Checkbox to recognize the new Macro?
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > What changes the amount - someone editing the cell with the amount?
> > > >
> > > >
> > > > right click on the sheet tab and select view code.
> > > >
> > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > if Target.Address = "$B$9" then
> > > > If Target.Value >0 then
> > > > me.checkbox1.Value = True
> > > > else
> > > > me.checkbox1.Value = False
> > > > end if
> > > > End if
> > > > end sub
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > >
> > > > "Nausett" wrote:
> > > >
> > > > > I want to be able to have a check appear in the box if I have an
amount >0.

Thanks Tom. I was able to get it to work. I was using the the wrong
checkbox. Thanks again for all your help

"Tom Ogilvy" wrote:

> Highly likely that you are doing something wrong. I put a checkbox on my
> worksheet and named it Checkbox1. I then pasted in the code from the email
> into the sheet module for that worksheet.
>
> I entered a 3 in B9, the checkbox was checked.
>
> I entered a 0 in B9,. the checkbox was unchecked.
>
> --
> Regards,
> Tom Ogilvy
>
> "Nausett" <[email protected]> wrote in message
> news:[email protected]...
> > I'm getting a Compile Error: Method or Data Member Not Found. Am I doing
> > something wrong?
> > Thanks,
> >
> > "Tom Ogilvy" wrote:
> >
> > > The checkbox is a passive victim. Given the assumptions I made, if you
> have
> > > a checkbox named checkbox1 (from the control toolbox toolbar) and it is
> > > located on the sheet with the code, and you have a cell B9, and with the
> code
> > > in the sheet module as I described, you change the value of B9 to 3 (as
> an
> > > example), checkbox1 should get checked.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "Nausett" wrote:
> > >
> > > > How do I get Excel the Checkbox to recognize the new Macro?
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > What changes the amount - someone editing the cell with the amount?
> > > > >
> > > > >
> > > > > right click on the sheet tab and select view code.
> > > > >
> > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > if Target.Address = "$B$9" then
> > > > > If Target.Value >0 then
> > > > > me.checkbox1.Value = True
> > > > > else
> > > > > me.checkbox1.Value = False
> > > > > end if
> > > > > End if
> > > > > end sub
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Nausett" wrote:
> > > > >
> > > > > > I want to be able to have a check appear in the box if I have an
> amount >0.
>
>
>

I have another question. If the reference cell is formula driven, for
example, =IF(Input!B1="Drawdown",Input!B5," "). What would I need to do to
make the macro work?

"Nausett" wrote:

> Thanks Tom. I was able to get it to work. I was using the the wrong
> checkbox. Thanks again for all your help
>
> "Tom Ogilvy" wrote:
>
> > Highly likely that you are doing something wrong. I put a checkbox on my
> > worksheet and named it Checkbox1. I then pasted in the code from the email
> > into the sheet module for that worksheet.
> >
> > I entered a 3 in B9, the checkbox was checked.
> >
> > I entered a 0 in B9,. the checkbox was unchecked.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Nausett" <[email protected]> wrote in message
> > news:[email protected]...
> > > I'm getting a Compile Error: Method or Data Member Not Found. Am I doing
> > > something wrong?
> > > Thanks,
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > The checkbox is a passive victim. Given the assumptions I made, if you
> > have
> > > > a checkbox named checkbox1 (from the control toolbox toolbar) and it is
> > > > located on the sheet with the code, and you have a cell B9, and with the
> > code
> > > > in the sheet module as I described, you change the value of B9 to 3 (as
> > an
> > > > example), checkbox1 should get checked.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "Nausett" wrote:
> > > >
> > > > > How do I get Excel the Checkbox to recognize the new Macro?
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > What changes the amount - someone editing the cell with the amount?
> > > > > >
> > > > > >
> > > > > > right click on the sheet tab and select view code.
> > > > > >
> > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > if Target.Address = "$B$9" then
> > > > > > If Target.Value >0 then
> > > > > > me.checkbox1.Value = True
> > > > > > else
> > > > > > me.checkbox1.Value = False
> > > > > > end if
> > > > > > End if
> > > > > > end sub
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Nausett" wrote:
> > > > > >
> > > > > > > I want to be able to have a check appear in the box if I have an
> > amount >0.
> >
> >
> >

Private Sub Worksheet_Calculate()
If Me.Range("B9").Value > 0 then
me.checkbox1.Value = True
else
me.checkbox1.Value = False
end if
End if
end sub

Use the calculate event. This must be in the sheet module as well.

--
Regards,
Tom Ogilvy


"Nausett" <[email protected]> wrote in message
news:[email protected]...
> I have another question. If the reference cell is formula driven, for
> example, =IF(Input!B1="Drawdown",Input!B5," "). What would I need to do
to
> make the macro work?
>
> "Nausett" wrote:
>
> > Thanks Tom. I was able to get it to work. I was using the the wrong
> > checkbox. Thanks again for all your help
> >
> > "Tom Ogilvy" wrote:
> >
> > > Highly likely that you are doing something wrong. I put a checkbox on
my
> > > worksheet and named it Checkbox1. I then pasted in the code from the
email
> > > into the sheet module for that worksheet.
> > >
> > > I entered a 3 in B9, the checkbox was checked.
> > >
> > > I entered a 0 in B9,. the checkbox was unchecked.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > > "Nausett" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > I'm getting a Compile Error: Method or Data Member Not Found. Am I
doing
> > > > something wrong?
> > > > Thanks,
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > The checkbox is a passive victim. Given the assumptions I made,
if you
> > > have
> > > > > a checkbox named checkbox1 (from the control toolbox toolbar) and
it is
> > > > > located on the sheet with the code, and you have a cell B9, and
with the
> > > code
> > > > > in the sheet module as I described, you change the value of B9 to
3 (as
> > > an
> > > > > example), checkbox1 should get checked.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > > "Nausett" wrote:
> > > > >
> > > > > > How do I get Excel the Checkbox to recognize the new Macro?
> > > > > >
> > > > > > "Tom Ogilvy" wrote:
> > > > > >
> > > > > > > What changes the amount - someone editing the cell with the
amount?
> > > > > > >
> > > > > > >
> > > > > > > right click on the sheet tab and select view code.
> > > > > > >
> > > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > > if Target.Address = "$B$9" then
> > > > > > > If Target.Value >0 then
> > > > > > > me.checkbox1.Value = True
> > > > > > > else
> > > > > > > me.checkbox1.Value = False
> > > > > > > end if
> > > > > > > End if
> > > > > > > end sub
> > > > > > >
> > > > > > > --
> > > > > > > Regards,
> > > > > > > Tom Ogilvy
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Nausett" wrote:
> > > > > > >
> > > > > > > > I want to be able to have a check appear in the box if I
have an
> > > amount >0.
> > >
> > >
> > >

It worked like a charm. Thanks so much Tom.

"Tom Ogilvy" wrote:

> Private Sub Worksheet_Calculate()
> If Me.Range("B9").Value > 0 then
> me.checkbox1.Value = True
> else
> me.checkbox1.Value = False
> end if
> End if
> end sub
>
> Use the calculate event. This must be in the sheet module as well.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Nausett" <[email protected]> wrote in message
> news:[email protected]...
> > I have another question. If the reference cell is formula driven, for
> > example, =IF(Input!B1="Drawdown",Input!B5," "). What would I need to do
> to
> > make the macro work?
> >
> > "Nausett" wrote:
> >
> > > Thanks Tom. I was able to get it to work. I was using the the wrong
> > > checkbox. Thanks again for all your help
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Highly likely that you are doing something wrong. I put a checkbox on
> my
> > > > worksheet and named it Checkbox1. I then pasted in the code from the
> email
> > > > into the sheet module for that worksheet.
> > > >
> > > > I entered a 3 in B9, the checkbox was checked.
> > > >
> > > > I entered a 0 in B9,. the checkbox was unchecked.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > > "Nausett" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > I'm getting a Compile Error: Method or Data Member Not Found. Am I
> doing
> > > > > something wrong?
> > > > > Thanks,
> > > > >
> > > > > "Tom Ogilvy" wrote:
> > > > >
> > > > > > The checkbox is a passive victim. Given the assumptions I made,
> if you
> > > > have
> > > > > > a checkbox named checkbox1 (from the control toolbox toolbar) and
> it is
> > > > > > located on the sheet with the code, and you have a cell B9, and
> with the
> > > > code
> > > > > > in the sheet module as I described, you change the value of B9 to
> 3 (as
> > > > an
> > > > > > example), checkbox1 should get checked.
> > > > > >
> > > > > > --
> > > > > > Regards,
> > > > > > Tom Ogilvy
> > > > > >
> > > > > >
> > > > > > "Nausett" wrote:
> > > > > >
> > > > > > > How do I get Excel the Checkbox to recognize the new Macro?
> > > > > > >
> > > > > > > "Tom Ogilvy" wrote:
> > > > > > >
> > > > > > > > What changes the amount - someone editing the cell with the
> amount?
> > > > > > > >
> > > > > > > >
> > > > > > > > right click on the sheet tab and select view code.
> > > > > > > >
> > > > > > > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > > > > > > if Target.Address = "$B$9" then
> > > > > > > > If Target.Value >0 then
> > > > > > > > me.checkbox1.Value = True
> > > > > > > > else
> > > > > > > > me.checkbox1.Value = False
> > > > > > > > end if
> > > > > > > > End if
> > > > > > > > end sub
> > > > > > > >
> > > > > > > > --
> > > > > > > > Regards,
> > > > > > > > Tom Ogilvy
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > > "Nausett" wrote:
> > > > > > > >
> > > > > > > > > I want to be able to have a check appear in the box if I
> have an
> > > > amount >0.
> > > >
> > > >
> > > >
>
>
>