Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CheckBox (Excel 2002 on Win XP Pro)

    I received a spreadsheet (sample attached) of nearly 10,000 entries. The idea is for me to import the data into Access 2K and remove all entries checked. The task would be simple if the checkbox had a value such as True/False, Yes/No. I can find no way to make a formula or check the value with VBA to maybe put an "x" in the third column so I can import it. Someone spent a lot of time making check marks and I hope there is some way not to have another person spend the same amount of time inputting something which can be imported or filtered on.

    Any advice other than the manual method would be greatly appreciated

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: CheckBox (Excel 2002 on Win XP Pro)

    This code will link the comboboxes to the cell underneath them. Then you can look at that cell for true/false or filter or whatever. Once they are "linked" they are "live" and will reflect any changes. You should have to only run this once.

    Steve

    <pre>Option Explicit
    Sub LinkCB()
    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
    With shp
    If .Type = msoFormControl Then
    If .FormControlType = xlCheckBox Then
    .Select
    Selection.LinkedCell = .BottomRightCell.Address
    End If
    End If
    End With
    Next
    ActiveCell.Select
    End Sub</pre>


  3. #3
    Lounger
    Join Date
    Oct 2001
    Location
    Upper NY State, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CheckBox (Excel 2002 on Win XP Pro)

    Perfect Solution. Since the post, I was able to manually link an adjacent cell to an individual checkbox and tried to record a macro to change several of the checkboxes, but could not find a pattern I could put in a vba sub to process the entire sheet.

    Thank you very much! <img src=/S/cloud9.gif border=0 alt=cloud9 width=25 height=23>

Posting Permissions

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