☰

更改复选框可见性不起作用

tag-icon • tag-icon microsoft-excel vba
更改复选框可见性不起作用

我有以下代码隐藏工作表中的行，但工作表中动态创建的复选框仍然可见。有什么想法吗？

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

因此，目前我在工作表上有 ckboxPrintLabels4 到 16。它们无法更改可见性。尽管 msgbox 报告它们的可见性为 false...???

答案1

您是否检查过所有可见的复选框实际上都名为 ckboxPrintLabelsX 等

我运行了你的代码两次，因为在第一次运行中，当我分离出你后来注释的代码时，我没有正确命名框。第二次运行正确地隐藏了标题为 ckboxPrintLabelsX 的复选框，但其他标题不同的复选框（复选框 1 等）仍然可见

快速谷歌搜索发现了一个类似的问题http://www.teachexcel.com/excel-help/excel-how-to.php?i=103984。在这种情况下，由于复选框堆叠在隐藏行上，因此似乎有一个复选框仍然可见于顶部。

创建代码时出错，创建了多个同名复选框。这些复选框会堆叠。隐藏时，它只对一组控件有效。

    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

相关内容