Results 1 to 8 of 8

Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

This is a discussion on Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range. within the Excel Questions forums, part of the Question Forums category; So i have a row of 7 checkboxes, inserted using “Insert – Form Controls” dialog in excel 2007. Each checkbox ...

  1. #1
    New Member
    Join Date
    Mar 2010
    Posts
    4

    Default Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    So i have a row of 7 checkboxes, inserted using “Insert – Form Controls” dialog in excel 2007.
    Each checkbox sits above a cell, and each checkbox has been formatted so that it has a “cell link” to the cell directly beneath it.
    The checkboxes are not grouped or anything, they are all separate.
    I have several rows of checkboxes similar to this.

    I would like to create a macro that when run, will uncheck all the boxes that are within a certain range, for example, from A1 to G1.

    I have found VBA that will clear all checkboxes on a sheet, but the key for me is to be able to specify which checkboxes get cleared when i run the macro.

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,073

    Default Re: Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    If you do have cell links all you should need to do is change the value in the linked cell, which should be pretty straightforward.
    Code:
    Range("A1:G1").Value = ""
    Not 100% sure if "" is the right thing to use - I'm testing it at the moment.

    I'll post back.
    If posting code please use code tags.

  3. #3
    New Member
    Join Date
    Mar 2010
    Posts
    4

    Default Re: Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    By the way, I'm totally new to vba and macro's, please be as explicit as possible in any suggestion, if that's ok? For example, what exactly should i do with the code snippet above?

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,073

    Default Re: Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    Could you be as explicit as you can about what you are actually trying to achieve?

    You originally mentioned a named range then you refer to A1:G1.

    Also you seem to be referring to multiple rows of multiple checkboxes.

    That might not be a good idea.

    The more checkboxes you have the harder it's going to be to deal with things, and it might also increase the size of the file.

    Perhaps there's another approach you could take to do what you want.
    If posting code please use code tags.

  5. #5
    New Member
    Join Date
    Mar 2010
    Posts
    4

    Default Re: Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    apologies, i see how my "named range" confuses matters. Basically, i want to assign a macro to a button, that when pressed, clears all the ticks from the boxes that are sitting on top of cells A1 to G1.

  6. #6
    Board Regular
    Join Date
    Jan 2008
    Posts
    8,330

    Default Re: Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    Hi, Try this:-
    Alter Range "Rng" to suit.
    Code:
    Sub MG04Mar32
    Dim cb As Object, rng As Range
    On Error Resume Next
    Set rng = Range("A2:A4")
    For Each cb In ActiveSheet.CheckBoxes
        If Not Intersect(Range(cb.LinkedCell), rng) Is Nothing Then
            cb.Value = False
        End If
    Next cb
    End Sub
    Regards Mick

  7. #7
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    66,073

    Default Re: Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    Just add a button from the Forms toolbar like you added the checkboxes.

    Right click it, select Assign macro..., and press the New button.

    You should then see something like this.
    Code:
    Sub Button8_Click()
    
    End Sub
    That's where the code I posted should go.

    Mind you I was sort of serious about the multiple checkboxes thing and I've got a feeling there's more to whatever you want to do.
    If posting code please use code tags.

  8. #8
    New Member
    Join Date
    Mar 2010
    Posts
    4

    Default Re: Excel Checkbox Question. Please help me write a macro that will selectively uncheck certain boxes within a named range.

    Quick update here. The code posted by MickG works perfectly, thank you very much. Thanks also to Norie for clarifying re the button/code placement.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com