On Change (Checkboxes) or something similar.

Hi Everyone.

I have a variable number (say 50-100) of lines of data that is automatically
imported from a file into my worksheet on a daily basis. When this happens, I
have a sub that runs and adds a checkbox next to each of the 50-100
lines...i.e. each line has, following the code running, a checkbox to
determine whether to include or not.

I want to be able to detect when a user checks one of these boxes, to ensure
that they check no more than three in total (of the 50+). I've tried
detecting whether the underlying cell (i.e. the cell that has the true/false)
changes, but the code only works when it is changed manually - not when it
changes due to the checkbox itself being clicked/unclicked. I also thought
about making code for EACH of the checkboxes individually, but as I never
know how many there will be this is impractical and not advised I don't think.

Can anyone offer any advice?

Thanks in advance,

David

avid,

You could assign the same macro to all checkboxes.

If you need to know which checkbox called it, use Application.Caller

MsgBox Application.Caller

--
HTH

Bob Phillips

"David" <[email protected]> wrote in message
news:[email protected]...
> Hi Everyone.
>
> I have a variable number (say 50-100) of lines of data that is
automatically
> imported from a file into my worksheet on a daily basis. When this
happens, I
> have a sub that runs and adds a checkbox next to each of the 50-100
> lines...i.e. each line has, following the code running, a checkbox to
> determine whether to include or not.
>
> I want to be able to detect when a user checks one of these boxes, to
ensure
> that they check no more than three in total (of the 50+). I've tried
> detecting whether the underlying cell (i.e. the cell that has the
true/false)
> changes, but the code only works when it is changed manually - not when it
> changes due to the checkbox itself being clicked/unclicked. I also thought
> about making code for EACH of the checkboxes individually, but as I never
> know how many there will be this is impractical and not advised I don't
think.
>
> Can anyone offer any advice?
>
> Thanks in advance,
>
> David

Thanks Bob.

So, I understand from reading about Application.Caller that it will return
the specific checkbox, or control, that was checked.

However, where do I put this code? Normally I'd do a Private Sub
CheckBox29_Click() for example...but I'm not sure how to do this using
Application.Caller.

So I tried to do a when worksheet selection change, with the
Application.Caller in it...but this gives me a type mismatch (Win XP, Excel
2003).

I'm a little confused on how to implement this. Basically I just want the
same macro/sub to run each time one of my checkboxes is selected/deselected
(as I think you understood already). I've tried searching for some code
examples using Application.Caller, but can't seem to find too much.

I would really appreciate it if you could provide some more guidance?

Thanks!

David

"Bob Phillips" wrote:

> avid,
>
> You could assign the same macro to all checkboxes.
>
> If you need to know which checkbox called it, use Application.Caller
>
> MsgBox Application.Caller
>
> --
> HTH
>
> Bob Phillips
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Everyone.
> >
> > I have a variable number (say 50-100) of lines of data that is
> automatically
> > imported from a file into my worksheet on a daily basis. When this
> happens, I
> > have a sub that runs and adds a checkbox next to each of the 50-100
> > lines...i.e. each line has, following the code running, a checkbox to
> > determine whether to include or not.
> >
> > I want to be able to detect when a user checks one of these boxes, to
> ensure
> > that they check no more than three in total (of the 50+). I've tried
> > detecting whether the underlying cell (i.e. the cell that has the
> true/false)
> > changes, but the code only works when it is changed manually - not when it
> > changes due to the checkbox itself being clicked/unclicked. I also thought
> > about making code for EACH of the checkboxes individually, but as I never
> > know how many there will be this is impractical and not advised I don't
> think.
> >
> > Can anyone offer any advice?
> >
> > Thanks in advance,
> >
> > David
>
>
>

David,

This depends upon where you created the checkbox from.

If it is a Forms checkbox, you assign the macro (right-click the control,
Assign Macro), and then use that in the macro

Private Sub ProcessCbs()
MsgBox Application.Caller
End Sub

However, if it is a checkbox from the control toolbox, each of these has a
click event that you can program, but here you get in multi procedures.

I suggest you use forms checkboxes. If you are doing it programmatically,
you can use something like

With ActiveSheet
.CheckBoxes.Add(.Range("H1").Left, Range("H1").Top, 100, 16).Select
Selection.OnAction = "Macro1"
End With


--
HTH

Bob Phillips

"David" <[email protected]> wrote in message
news:[email protected]...
> Thanks Bob.
>
> So, I understand from reading about Application.Caller that it will return
> the specific checkbox, or control, that was checked.
>
> However, where do I put this code? Normally I'd do a Private Sub
> CheckBox29_Click() for example...but I'm not sure how to do this using
> Application.Caller.
>
> So I tried to do a when worksheet selection change, with the
> Application.Caller in it...but this gives me a type mismatch (Win XP,
Excel
> 2003).
>
> I'm a little confused on how to implement this. Basically I just want the
> same macro/sub to run each time one of my checkboxes is
selected/deselected
> (as I think you understood already). I've tried searching for some code
> examples using Application.Caller, but can't seem to find too much.
>
> I would really appreciate it if you could provide some more guidance?
>
> Thanks!
>
> David
>
> "Bob Phillips" wrote:
>
> > avid,
> >
> > You could assign the same macro to all checkboxes.
> >
> > If you need to know which checkbox called it, use Application.Caller
> >
> > MsgBox Application.Caller
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "David" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Everyone.
> > >
> > > I have a variable number (say 50-100) of lines of data that is
> > automatically
> > > imported from a file into my worksheet on a daily basis. When this
> > happens, I
> > > have a sub that runs and adds a checkbox next to each of the 50-100
> > > lines...i.e. each line has, following the code running, a checkbox to
> > > determine whether to include or not.
> > >
> > > I want to be able to detect when a user checks one of these boxes, to
> > ensure
> > > that they check no more than three in total (of the 50+). I've tried
> > > detecting whether the underlying cell (i.e. the cell that has the
> > true/false)
> > > changes, but the code only works when it is changed manually - not
when it
> > > changes due to the checkbox itself being clicked/unclicked. I also
thought
> > > about making code for EACH of the checkboxes individually, but as I
never
> > > know how many there will be this is impractical and not advised I
don't
> > think.
> > >
> > > Can anyone offer any advice?
> > >
> > > Thanks in advance,
> > >
> > > David
> >
> >
> >

Bob...you are the best!

Thanks so much for your help. You are right, I was using ActiveX rather than
the form controls. Having now made the swtich in my code, everything is
working perfectly.

Much appreciated.

David

"Bob Phillips" wrote:

> David,
>
> This depends upon where you created the checkbox from.
>
> If it is a Forms checkbox, you assign the macro (right-click the control,
> Assign Macro), and then use that in the macro
>
> Private Sub ProcessCbs()
> MsgBox Application.Caller
> End Sub
>
> However, if it is a checkbox from the control toolbox, each of these has a
> click event that you can program, but here you get in multi procedures.
>
> I suggest you use forms checkboxes. If you are doing it programmatically,
> you can use something like
>
> With ActiveSheet
> .CheckBoxes.Add(.Range("H1").Left, Range("H1").Top, 100, 16).Select
> Selection.OnAction = "Macro1"
> End With
>
>
> --
> HTH
>
> Bob Phillips
>
> "David" <[email protected]> wrote in message
> news:[email protected]...
> > Thanks Bob.
> >
> > So, I understand from reading about Application.Caller that it will return
> > the specific checkbox, or control, that was checked.
> >
> > However, where do I put this code? Normally I'd do a Private Sub
> > CheckBox29_Click() for example...but I'm not sure how to do this using
> > Application.Caller.
> >
> > So I tried to do a when worksheet selection change, with the
> > Application.Caller in it...but this gives me a type mismatch (Win XP,
> Excel
> > 2003).
> >
> > I'm a little confused on how to implement this. Basically I just want the
> > same macro/sub to run each time one of my checkboxes is
> selected/deselected
> > (as I think you understood already). I've tried searching for some code
> > examples using Application.Caller, but can't seem to find too much.
> >
> > I would really appreciate it if you could provide some more guidance?
> >
> > Thanks!
> >
> > David
> >
> > "Bob Phillips" wrote:
> >
> > > avid,
> > >
> > > You could assign the same macro to all checkboxes.
> > >
> > > If you need to know which checkbox called it, use Application.Caller
> > >
> > > MsgBox Application.Caller
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "David" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi Everyone.
> > > >
> > > > I have a variable number (say 50-100) of lines of data that is
> > > automatically
> > > > imported from a file into my worksheet on a daily basis. When this
> > > happens, I
> > > > have a sub that runs and adds a checkbox next to each of the 50-100
> > > > lines...i.e. each line has, following the code running, a checkbox to
> > > > determine whether to include or not.
> > > >
> > > > I want to be able to detect when a user checks one of these boxes, to
> > > ensure
> > > > that they check no more than three in total (of the 50+). I've tried
> > > > detecting whether the underlying cell (i.e. the cell that has the
> > > true/false)
> > > > changes, but the code only works when it is changed manually - not
> when it
> > > > changes due to the checkbox itself being clicked/unclicked. I also
> thought
> > > > about making code for EACH of the checkboxes individually, but as I
> never
> > > > know how many there will be this is impractical and not advised I
> don't
> > > think.
> > > >
> > > > Can anyone offer any advice?
> > > >
> > > > Thanks in advance,
> > > >
> > > > David
> > >
> > >
> > >
>
>
>

Glad we sorted it David.

Regards

Bob

"David" <[email protected]> wrote in message
news:[email protected]...
> Bob...you are the best!
>
> Thanks so much for your help. You are right, I was using ActiveX rather
than
> the form controls. Having now made the swtich in my code, everything is
> working perfectly.
>
> Much appreciated.
>
> David
>
> "Bob Phillips" wrote:
>
> > David,
> >
> > This depends upon where you created the checkbox from.
> >
> > If it is a Forms checkbox, you assign the macro (right-click the
control,
> > Assign Macro), and then use that in the macro
> >
> > Private Sub ProcessCbs()
> > MsgBox Application.Caller
> > End Sub
> >
> > However, if it is a checkbox from the control toolbox, each of these has
a
> > click event that you can program, but here you get in multi procedures.
> >
> > I suggest you use forms checkboxes. If you are doing it
programmatically,
> > you can use something like
> >
> > With ActiveSheet
> > .CheckBoxes.Add(.Range("H1").Left, Range("H1").Top, 100, 16).Select
> > Selection.OnAction = "Macro1"
> > End With
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "David" <[email protected]> wrote in message
> > news:[email protected]...
> > > Thanks Bob.
> > >
> > > So, I understand from reading about Application.Caller that it will
return
> > > the specific checkbox, or control, that was checked.
> > >
> > > However, where do I put this code? Normally I'd do a Private Sub
> > > CheckBox29_Click() for example...but I'm not sure how to do this using
> > > Application.Caller.
> > >
> > > So I tried to do a when worksheet selection change, with the
> > > Application.Caller in it...but this gives me a type mismatch (Win XP,
> > Excel
> > > 2003).
> > >
> > > I'm a little confused on how to implement this. Basically I just want
the
> > > same macro/sub to run each time one of my checkboxes is
> > selected/deselected
> > > (as I think you understood already). I've tried searching for some
code
> > > examples using Application.Caller, but can't seem to find too much.
> > >
> > > I would really appreciate it if you could provide some more guidance?
> > >
> > > Thanks!
> > >
> > > David
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > avid,
> > > >
> > > > You could assign the same macro to all checkboxes.
> > > >
> > > > If you need to know which checkbox called it, use Application.Caller
> > > >
> > > > MsgBox Application.Caller
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "David" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi Everyone.
> > > > >
> > > > > I have a variable number (say 50-100) of lines of data that is
> > > > automatically
> > > > > imported from a file into my worksheet on a daily basis. When this
> > > > happens, I
> > > > > have a sub that runs and adds a checkbox next to each of the
50-100
> > > > > lines...i.e. each line has, following the code running, a checkbox
to
> > > > > determine whether to include or not.
> > > > >
> > > > > I want to be able to detect when a user checks one of these boxes,
to
> > > > ensure
> > > > > that they check no more than three in total (of the 50+). I've
tried
> > > > > detecting whether the underlying cell (i.e. the cell that has the
> > > > true/false)
> > > > > changes, but the code only works when it is changed manually - not
> > when it
> > > > > changes due to the checkbox itself being clicked/unclicked. I also
> > thought
> > > > > about making code for EACH of the checkboxes individually, but as
I
> > never
> > > > > know how many there will be this is impractical and not advised I
> > don't
> > > > think.
> > > > >
> > > > > Can anyone offer any advice?
> > > > >
> > > > > Thanks in advance,
> > > > >
> > > > > David
> > > >
> > > >
> > > >
> >
> >
> >