+ Reply to Thread
Results 1 to 6 of 6

On Change (Checkboxes) or something similar.

  1. #1
    David
    Guest

    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

  2. #2
    Bob Phillips
    Guest

    Re: 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" <David@discussions.microsoft.com> wrote in message
    news:C0A985EE-427E-4341-97CC-2A572D6093AA@microsoft.com...
    > 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




  3. #3
    David
    Guest

    Re: 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" <David@discussions.microsoft.com> wrote in message
    > news:C0A985EE-427E-4341-97CC-2A572D6093AA@microsoft.com...
    > > 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

    >
    >
    >


  4. #4
    Bob Phillips
    Guest

    Re: 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" <David@discussions.microsoft.com> wrote in message
    news:4554757F-C037-4683-B083-9213A2656820@microsoft.com...
    > 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" <David@discussions.microsoft.com> wrote in message
    > > news:C0A985EE-427E-4341-97CC-2A572D6093AA@microsoft.com...
    > > > 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

    > >
    > >
    > >




  5. #5
    David
    Guest

    Re: 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" <David@discussions.microsoft.com> wrote in message
    > news:4554757F-C037-4683-B083-9213A2656820@microsoft.com...
    > > 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" <David@discussions.microsoft.com> wrote in message
    > > > news:C0A985EE-427E-4341-97CC-2A572D6093AA@microsoft.com...
    > > > > 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
    > > >
    > > >
    > > >

    >
    >
    >


  6. #6
    Bob Phillips
    Guest

    Re: On Change (Checkboxes) or something similar.

    Glad we sorted it David.

    Regards

    Bob

    "David" <David@discussions.microsoft.com> wrote in message
    news:41DEF0E0-D3AE-4CD8-9A0D-444C38262BA7@microsoft.com...
    > 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" <David@discussions.microsoft.com> wrote in message
    > > news:4554757F-C037-4683-B083-9213A2656820@microsoft.com...
    > > > 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" <David@discussions.microsoft.com> wrote in message
    > > > > news:C0A985EE-427E-4341-97CC-2A572D6093AA@microsoft.com...
    > > > > > 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
    > > > >
    > > > >
    > > > >

    > >
    > >
    > >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1