Results 1 to 11 of 11
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Fake check boxed (Office 2002 XP SP2)

    Is it possible to copy 10 records including the check boxes into a new workbook? Zip the new workbook, it should be less than 100 KB.

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fake check boxed (Office 2002 XP SP2)

    If you ZIP the workbook, is it still over 100K? I think this is going to be tough to solve without seeing the workbook.
    Legare Coleman

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

    Fake check boxed (Office 2002 XP SP2)

    I cannot attach the file since it's over 1 meg. It only contains 10 records and I see no compact and repair inExcel. I have "inherrited" a file of about 100,000 records. There are two columns, one is a company name column and the next column has a "checkbox" in it. The idea was for someone to identify which records need to be deleted form the worksheet then into ms access (which is my specialty).

    I copied the records to a new workbook, and it should be visible. I thought I tried to do this before, I must have messed up.

    I cannot attach the spreadseet even with the10 records in it. it still sits at over one Meg.

    OK, bottom line, the visible check box is nothing which stores a 0, 1 or -1. It's an Excel visual item. I cannot sort by the check box. I feel the check box column is an image, not a value. What I need to do is to find a way to flag a third column, put a real value in it, import it into access and nuke the checked records.

    I would be more than willing to send a copy of the short version of the .xls to any interested gurus.

    I have to fix this work flow by next year. So I need to change the spreadsheet or put it into Access where it belongs in the first place.

    I am pretty good with vba in access so I'm not scared of a code solution. I'm sorry, with Access a checkbox is a checkbos with a value, with Excel, I guess the rules are 'there are no rules, only visuals.'

    data check
    order0 x
    order1
    order3 x

    Dave

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

    Re: Fake check boxed (Office 2002 XP SP2)

    They are real check boxes. Their values are either TRUE or FALSE. Unfortunately, the person who designed the workbook hasn't bothered to define cell links for the check boxes; if that had been done, there would have been a column of TRUE/FALSE values.
    In the workbook attached by you the situation is very unattractive: there are 11 data rows but 12 check boxes, some partly overlapping. This will make it hard to determine which check box corresponds to which row. Is the original workbook like that too?

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

    Re: Fake check boxed (Office 2002 XP SP2)

    Hans,

    Yes, all the cells in the sample .xls are the same. There are over 100,000 records. I find it hard to believe I cannot determine a difference in the second column and then burn a usable item in, say, column C. If this has to be manual, so be it.

    OK. If there is no way to vbasic this one out, what should I recommend the "creator" of the spreadsheet for next year? Would and "x" work or perhaps just delete the record instead of flagging it.

    Should we have the need to flag it, how can I recommend the source providor on how to properly make a check box which can be sorted in a binary manner.

    I am so sorry to dump an issue like this on this great forum. I can forward the entire spreadsheet to other than this forum.

    Is the check box an image or something active?

    Thank you so much for your help.

    Dave

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

    Re: Fake check boxed (Office 2002 XP SP2)

    I already wrote that they are "real" check boxes - they are controls from the Forms toolbar.
    It may be possible to write a VBA routine, but it is not clear what the check boxes mean. Is the first check box useless, or are all check boxes shifted up, so that the first check box corresponds to row 2, etc.?

    BTW How can you have over 100,000 records in a worksheet? There are only 65,536 rows in a worksheet. Or do you mean that you have several worksheets?

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

    Re: Fake check boxed (Office 2002 XP SP2)

    Yes, there are many worksheets involved--between 3 to 4 thousand records per worsheet

    . Now that I have looked at what appeared in my attachment, the viaual is incorrect. In the live data actually has the name of the company in column A and a checkbox in Column B. There is no overlap or corner to corner correlation.

    I wiish I could post the whole thin, but it's too big.

    Dave

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

    Re: Fake check boxed (Office 2002 XP SP2)

    OK, let's try a simple method first.

    Create a backup of your workbook.
    Activate one of the worksheets with data.
    Copy the macro below into a module and run it (position the insertion point inside the Sub and press F5)
    Check VERY carefully whether the results in column C are correct.
    If not, more complicated code is necessary.

    Sub FillC()
    Dim i As Long
    Dim wsh As Worksheet
    Set wsh = ActiveSheet
    For i = 1 To wsh.Shapes.Count
    wsh.Shapes(i).ControlFormat.LinkedCell = "C" & i
    Next i
    Set wsh = Nothing
    End Sub

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

    Re: Fake check boxed (Office 2002 XP SP2)

    I'm glad it worked. For next year, simple "x" in the rows to be deleted would be simpler. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fake check boxed (Office 2002 XP SP2)

    And to quickly get rid of the now obsolete checkboxes, hit F5 (Goto), special, objects, OK. Then hit the del key.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Fake check boxed (Office 2002 XP SP2)

    Very wide picture reduced in size by HansV

    Hans,

    Thank you. It is solved. I ran your supplied code, but what happened was you were right, it looked on my copy the check was next to the item to be deleted. In actuality, it was one off. I took your code (below) and tagged on an extra +1 and it is correct. One simple filter and delete and we are done. I am not sure how to let the originators of this yearly beast to correct what it is they are doing.


    See results in attached .jpg.

    'Your recommendation:

    Sub FillC()
    Dim i As Long
    Dim wsh As Worksheet
    Set wsh = ActiveSheet
    For i = 1 To wsh.Shapes.Count
    wsh.Shapes(i).ControlFormat.LinkedCell = "C" & i
    Next i
    Set wsh = Nothing
    End Sub


    'the final tweek:

    Sub FillD()
    Dim i As Long
    Dim wsh As Worksheet
    Set wsh = ActiveSheet
    For i = 1 To wsh.Shapes.Count

    '
    'one minor change which I do not understand but will accept...
    '

    wsh.Shapes(i).ControlFormat.LinkedCell = "D" & (i + 1)
    Next i
    Set wsh = Nothing
    End Sub

    Sort them and delete them...done

    I thank you for you mentorship!

    Dave

Posting Permissions

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