Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    macro to clear check boxes (ms office 2000)

    Good Afternoon all,

    I am looking to create a macro that would allow me to reset a form that is 75 rows deep w/ questions on each row and check boxes of yes or no to be marked for each associate. Is there a way i can possibly use a looping program that would check the status of each box and reset them to "unchecked" in the properties? I am grabbing at straws. Any ideas would be appreciated.

    Thanks in advance for all the help. I am attaching a very small portion of the sheet so that you can hopefully understand the concept.

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: macro to clear check boxes (ms office 2000)

    Bill

    This should do it:

    Sub ClearAll()
    Dim obj

    For Each obj In ActiveSheet.OLEObjects
    If TypeOf obj.Object Is MSForms.CheckBox Then
    obj.Object.Value = False
    End If
    Next obj
    End Sub
    Jerry

  3. #3
    Star Lounger
    Join Date
    Sep 2004
    Location
    Wolverhampton, Staffordshire, England
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: macro to clear check boxes (ms office 2000)

    <P ID="edit" class=small>(Edited by Noirenex on 30-Sep-06 17:25. edit - added jezza's cleaner code to example)</P>Hi there, noticed you are using check boxes, radio buttons might be better, enclosed an example for you, check the code in VB and adapt it to meet your requirements, when you use a radio button, only one option from each can be selected, so if your expressions are boolean (Y/N) then these are best. Check the properties of the radio buttons in edit mode and each set of 2 is in it's own group, so that the system knows how many options each group has. Sorry if i wasn't clear on anything, feel free to ask q's.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to clear check boxes (ms office 2000)

    Since the check boxes are linked to cells in columns D and E, you can simply set the values of those cells to FALSE to clear the check boxes. In your example:

    Range("D8:E14") = False

    But in your spreadsheet, the user can tick both Y and N for each question. I would recommend to either use a single check box to be ticked for Y, cleared for N, or to use pairs of option buttons (radio buttons) instead. If you set the groupname for each pair to a unique name, they will act as pairs. To clear, you'd use

    Range("D8:E14").ClearContents

    See attached version.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro to clear check boxes (ms office 2000)

    Hello again ALL,

    This is why this is the best site for help !!! In less than 1 hour I have several good answers and reasons to make some changes. Thank you again to all.... I never cease to learn from this site. You guys are the BEST !

  6. #6
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro to clear check boxes (ms office 2000)

    Hello Again,

    I need to ask one other question here. I swithched all the checks to radio however the first button will not show the dot in the middle. All properties appear to be the same for all the other buttons. I have deleted the button and copied a new one in and it looks ok until I click on it then it goes away and will not reappear when used again. What might I be missing?
    Thanks

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: macro to clear check boxes (ms office 2000)

    Check the properties again:
    - What is the LinkedCell property?
    - What is the GroupName property?
    Each option button should have a different LinkedCell - you'll have problems if there are two with the same LinkedCell.
    And check carefully whether the GroupName property has been used consistently - each pair or option buttons corresponding to one question should have a common group name that is different from that of all other pairs.

  8. #8
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: macro to clear check boxes (ms office 2000)

    On a completely different track - why not make the form into a template. When a fresh version is required, just use the template.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  9. #9
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Harrisburg, Pennsylvania, USA
    Posts
    168
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: macro to clear check boxes (ms office 2000)

    That would work just as well. I always choose the long way around things [img]/forums/images/smilies/smile.gif[/img]

    Thanks for your thoughts

Posting Permissions

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