Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have the following code hiding rows in a sheet, but the sheet is left with its dynamically created checkboxes still visible. Any ideas?

Private Sub SpinButton1_Change()

Application.ScreenUpdating = False
week = Me.Range("b1").Value
countcell = ActiveSheet.Range("A:A").Cells.SpecialCells(xlCellTypeConstants).Count
For i = 4 To countcell
  pweek = Me.Cells(i, 2).Value    'load the planned week value
  mycont = "ckboxPrintLabels" & i
  If pweek <> week Then
    CheckBoxes(mycont).Visible = False
    Rows(i).EntireRow.Hidden = True
    MsgBox mycont & "= " & ActiveSheet.CheckBoxes(mycont).Visible
  Else
    Rows(i).EntireRow.Hidden = False
    ActiveSheet.CheckBoxes(mycont).Visible = True
  End If
  k = i
Next i
Application.ScreenUpdating = True
End Sub

So currently I have ckboxPrintLabels4 thru to 16 on the sheet. They fail to change visibility. Although the msgbox reports that they have visibility at false...???

share|improve this question
    
btw it's Excel 2003, and dynamic code for checkbox creation looks like this: ` ' snipped to fit in comments For Each cell In ws.Range("g" & Lower & ":g" & Upper) With ws.CheckBoxes.Add(cell.Left, _ cell.Top, cell.Width, cell.Height) .LinkedCell = "" .Interior.ColorIndex = xlNone .Caption = "" .name = "ckboxPrintLabels" & ckbox .Visible = True End With ckbox = ckbox + 1 Next Application.ScreenUpdating = True End Sub` –  Onyx Sep 19 '11 at 23:13
3  
please don't add [solved] and stuff the answer in the question section, you can add & accept your own answer –  Sathya Sep 21 '11 at 8:16
    
Sathya...thanks for clearing that up for me. It had me confused when unable to post again because of low score. –  Onyx Sep 22 '11 at 22:24

1 Answer 1

up vote 4 down vote accepted

Have you checked that all the visible checkboxes are actually named ckboxPrintLabelsX etc

I ran your code twice as in the first run I handn't named the boxes properly when I separated out your later commented code. The second run correctly hid the checkboxes with ckboxPrintLabelsX titles, but the other checkboxes with different titles (Check Box 1 etc) were still visible

A quick google throws up the a similar issue at http://www.teachexcel.com/excel-help/excel-how-to.php?i=103984. In this case as the checkboxes are stacked on the row hide, it appears one remanined visible on top.


Error was in creation code, creating more than one checkbox with same name. These would stack. When the hiding took place it only took effect on one set of controls.

    Public Sub addcheckboxes(ByVal Lower As String, ByVal Upper As String)
    Dim ws As Worksheet, myObjectname As String, addChk As Boolean

    Set ws = Workbooks("nursery.xls").Worksheets("Seeding")
     Application.ScreenUpdating = False

     ckbox = Lower
     ' add a checkbox for printing
     For Each cell In ws.Range("g" & Lower & ":g" & Upper)

     myObjectname = "ckboxPrintLabels" & ckbox
     addChk = True

     For Each ctrl In ActiveSheet.CheckBoxes
     ' nasty hack to overcome the limitations of vba in excel - no eval!
        If ctrl.name = myObjectname Then
        addChk = False ' if chkbox already exists
      ctrl.Visible = True 'switch to visible, as it may not be
      End If
       Next

    If addChk Then
    With ws.CheckBoxes.Add(cell.Left, _
     cell.Top, cell.Width, cell.Height)
     .LinkedCell = cell
     .Interior.ColorIndex = xlNone
     .Caption = ""
     .name = myObjectname
     .Visible = True
    End With
    End If
    ckbox = ckbox + 1
    Next
    Application.ScreenUpdating = True
    End Sub
share|improve this answer
    
Thanks, you are quite right. The creation is the problem. Edited main post to add good code. –  Onyx Sep 20 '11 at 4:02
    
cheers for the quick response and accept –  brettdj Sep 20 '11 at 4:13

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.