Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2006
    Posts
    5

    Exclamation Unanswered: Macro for Spreadsheet

    New to the forum, hoping for a helping hand.

    I have a spreadsheet that has numerous fields available for our staff to fill in, and a number of checkboxes to indicate selections, as well as a drop-down box.

    I would like to add a 'Clear All' button with a macro attached that will take all the fields that are unlocked and delete them (using
    Range "PageOneClear").Select
    Selection.ClearContents

    where PageOneClear is a range that I have defined as holding a number of cells. With the document being four pages in length, I have four ranges. In adding this macro to the button on my page, it clears the majority of fields, but not all of them. When I go in and check to ensure all cells are included in the range, they are.

    This 'Clear All' would also set all checkboxes to a value of 'Off' (which I so far cannot get done as I receive an error message "Method 'Select' of object 'Shape' failed). The code I am using to attempt this is ActiveSheet.Shapes("Check Box 87").Select
    With Selection
    .Value = xlOff
    .LinkedCell = ""
    .Display3DShading = True
    End With
    repeating this for each box on the page.

    If anyone could give me a hand and let me know if there is an easier way to do this, that would be greatly appreciated.

    Thanks in advance.

    Casey.

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    ActiveSheet.Shapes("Check Box 87").Select
    With Selection
    .Value = xlOff
    .LinkedCell = ""
    .Display3DShading = True
    End With


    In your code you are trying to select the Control "Check Box 87" then you are trying to act on the selection. If this worked you would change the parameters of the control. Not what you want to do. I think you get an error because you are using 'Shape' object when a checkbox is a control object.

    Most likely if you are using form controls on a spreadsheet you have them linked to cells to change the value in a cell. Use defined range names for the linked cells. Defining named ranges allows you to change the sheet around adding rows moving cells without having to change your code just for moving a cell.

    You do not need to act on the control to reset its value. If you change the value in the linked cell of a control the control will change to reflect this value. Therefore your code should manipulate the linked cell values to reset the controls.

    I created an example where I added 3 checkbox controls to the page and linked them each to a different cell. I named each cell with a defined range name: CB1Val,CB2Val,CB3Val. I wrote this macro and activate it from a button on the page. It changes the cell values to "False" unchecking each checkbox. Sub loopNames() takes this idea a step further by checking the defined names and resetting the value if the name matches a pattern of "CB#Val". This allows you to add or remove checkboxes without having to change the code or number of loop iterations as long as you define a range for each control following your control sequence with the defined range name.

    Code:
    Sub Macro1()
    '
      For i = 1 To 3
        cbRng = "CB" & i & "Val"
        Range(cbRng) = "False"
      Next
    '
    End Sub
    
    '
    Sub loopnames()
    
    For Each defName In Names
      If defName.Name Like "CB#Val" Then
         Range(defName).Value = "False"
      End If
    Next
    
    End Sub
    My test file is in the attached zip if you would like to see this example in action.
    Attached Files Attached Files
    Last edited by savbill; 11-28-06 at 23:26.
    ~

    Bill

Posting Permissions

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