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

On Change (Checkboxes) or something similar.
 

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

On Change (Checkboxes) or something similar.
 

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

On Change (Checkboxes) or something similar.
 

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

On Change (Checkboxes) or something similar.
 

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

On Change (Checkboxes) or something similar.
 

Glad we sorted it David.

Regards

Bob

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