Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Last Checkbox not hidden (Excel 2000 sp3)

    I have a spreadsheet with two columns used. Column B has checkboxes from the control toolbar. Column C has comments. I want the users to check some of the checkboxes then click a control button behind which I hide all the checkboxes and all the rows except the ones they checked. I have overcome some weird problem but for the most part it is working great. Only problem is, the last checkbox checked is not getting hidden. It doesn't matter which one I check last, that one doesn't get hidden. Here's the code I am using to hide them. Any ideas? I tried adding a line before the loop "range("A1").select" but it didn't help.
    I'm also open to suggestions for a better way to do this if you have any ideas.
    Thanks

    ' Hide checkboxes and non-selected rows...
    For I = 1 To 58
    oCkBx = "CheckBox" & I
    ActiveSheet.Shapes(oCkBx).Visible = False
    If Cells(I + 2, 2) = False Then
    Cells(I + 2, 2).Select
    Selection.EntireRow.Hidden = True
    End If
    Next

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

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    Could you post your workbook? It's hard to visualize this, and I'm too lazy too reconstruct the workbook myself. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  3. #3
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    I think so, try this.

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

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    You check boxes correspond to row 4, 5, ..., but Cells(I + 2, 2) starts in row 3 (for I = 1). You should use Cells(I + 3, 2) both times.

  5. #5
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    That's the first time I tried to post a workbook. I'm glad it worked. Sorry about that, I copied the code from my much bigger workbook and forgot to change that. Do you see what I described? if you click one of the checkboxes and then click the "Hide" button that one checkbox remains? If you click several checkboxes before clicking "Hide" the last checkbox remains unhidden.
    Thanks

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

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    I didn't, at first, because I clicked in a cell after clicking one or more check boxes, before clicking the command button. The problem you mention doesn't occur then. If I click the command button immediately after clicking a check box, that check box remains visible. I don't know how to avoid this, so the only recommendation I have is to click in a cell before clicking the command button.

  7. #7
    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: Last Checkbox not hidden (Excel 2000 sp3)

    Instead of the the SHAPES object try:

    ActiveSheet.OLEObjects(oCkBx).Visible = False

    That seemed to work for me.
    [i]note to XL97 users, I had to add the line:
    <pre>ActiveCell.select</pre>

    at the beginning of code to ensure that none of the objects have the focus. You can not unprotect sheets, or hide rows if a control has the focus in XL97

    Steve

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

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    Using OLEObjects instead of Shapes works fine in Excel 2002 too. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    (No need to select a cell before unprotecting the worksheet there.)

  9. #9
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    Hans

    ************************************************** **********
    <font color=blue>If I click the command button immediately after clicking a check box, that check box remains visible. I don't know how to avoid this</font color=blue>
    ************************************************** **********
    I haven't downloaded the attached file but what about using a Checkbox_click event to automatically shift the focus back to a cell in the spreadsheet each time the user clicks a checkbox. ??
    Something like:

    Private sub Checkbox1_Click()
    Range("A1").Select
    End Sub

    This would have to be written for each checkbox. 58 would be tiresome.
    So I would change this to

    Private Worksheet_Change (Target as Range)
    If Target.Count>1 then exit sub
    ' Assuming B3:B61 is the list of linked cells for the checkboxes:
    If Not Intersect(Target,Range("B3:B61") is Nothing then
    Range("A1").Select
    End If
    End Sub

    The OP might give this a try and post back how it works.

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

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    Thanks for this suggestion. Steve has already posted a very simple but effective workaround.

  11. #11
    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: Last Checkbox not hidden (Excel 2000 sp3)

    Due to a bug in XL97, Before I could even run the code, I had added code to select the activecell.

    Even having the code select a cell, still required the user to select a cell before running the code if the Shapes collection was used. The OleObjects code worked much better.

    Steve

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    WOW! Great fix! I was wondering if I could use something other than Shapes because it isn't specific to checkboxes. This isn't specific either but it sure cures the problem. Thanks!

  13. #13
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    Thanks Hans. Users being what they are (unpredictable), I can't count on them to click a cell before clicking the "Hide" button. I had hoped the "Range("A2").Select" statement would have the same effect but it doesn't.
    The OLEObject solution works great.
    Thanks

  14. #14
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    USA
    Posts
    223
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Last Checkbox not hidden (Excel 2000 sp3)

    Thanks to all for the great help and suggestions. I learn so much from this forum, you guys are the best!

  15. #15
    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: Last Checkbox not hidden (Excel 2000 sp3)

    <hr>Users being what they are (unpredictable), I can't count on them to click a cell before clicking the "Hide" button<hr>

    I disagree. I find user's very predictable. I could count on them to NOT click the cell before pressing the "hide" button <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    They will (predicatibly) do whatever will cause the most damage <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    Steve

Page 1 of 2 12 LastLast

Posting Permissions

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