+ Reply to Thread
Results 1 to 2 of 2

[SOLVED] Checkbox visibility setting fails

Hybrid View

  1. #1
    Registered User
    Join Date
    09-19-2011
    Location
    NZ
    MS-Off Ver
    Excel 2003
    Posts
    2

    [SOLVED] Checkbox visibility setting fails

    I am trying to make some dynamically created checkboxes disappear when I hide the row they are in, then reappear when I show the row. The code works for everything except hiding the checkboxes.

    
    Private Sub CommandButton1_Click()
       Application.ScreenUpdating = False
    
    k = 4
    For i = 4 To Application.CountA(Me.Range("b:b")) + 1
    
    Rows(i).EntireRow.Hidden = False
    k = k + 1
    Next i
    Call addcheckboxes("4", k - 1)
        Application.ScreenUpdating = True
    End Sub
    
    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
    msgbox ActiveSheet.CheckBoxes(mycont).Visible
    ActiveSheet.CheckBoxes(mycont).Visible = False ' << FAILS HERE
    Rows(i).EntireRow.Hidden = True
    
    Else
    Rows(i).EntireRow.Hidden = False
    ActiveSheet.CheckBoxes(mycont).Visible = True
    End If
    
    Next i
        Application.ScreenUpdating = True
    End Sub
    
    Private Sub Worksheet_Activate()
        Application.ScreenUpdating = False
    Dim iRow As Long, week As Integer
    
    Dim ws As Worksheet, acsh As Worksheet, gc As String
    
    
    Set ws = Workbooks("nursery.xls").Worksheets("Planning")
    week = Me.Range("b1").Value
    'Me.Range("a4:m256").Delete
    k = 4
    
    For i = 2 To Application.CountA(ws.Range("b:b"))
        iRow = ws.Cells(i, 11).Value
       If Me.Cells(k, 2).Value <> ws.Cells(i, 1).Value Then
       
        Me.Cells(k, 2).Value = ws.Cells(i, 1).Value ' planned week
        Me.Cells(k, 2).Font.ColorIndex = 5
        End If
        If Me.Cells(k, 1).Value <> ws.Cells(i, 7).Value Then
        Me.Cells(k, 1).Value = ws.Cells(i, 7).Value ' grower code
        Me.Cells(k, 1).Font.ColorIndex = 5
        End If
         If Me.Cells(k, 3).Value <> ws.Cells(i, 2).Value Then
        Me.Cells(k, 3).Value = ws.Cells(i, 2).Value ' crop
        Me.Cells(k, 3).Font.ColorIndex = 5
        End If
         If Me.Cells(k, 4).Value <> ws.Cells(i, 3).Value Then
        Me.Cells(k, 4).Value = ws.Cells(i, 3).Value 'variety
        Me.Cells(k, 4).Font.ColorIndex = 5
        End If
        If Me.Cells(k, 5).Value <> ws.Cells(i, 5).Value Then
         Me.Cells(k, 5).Value = ws.Cells(i, 5).Value 'tray
         Me.Cells(k, 5).Font.ColorIndex = 5
        End If
        If Me.Cells(k, 6).Value <> ws.Cells(i, 8).Value Then
         Me.Cells(k, 6).Value = ws.Cells(i, 8).Value 'trays ordered
          Me.Cells(k, 6).Font.ColorIndex = 5
        End If
     
        
        If Me.Cells(k, 9).Value <> ws.Cells(i, 14).Value Then
         Me.Cells(k, 9).Value = ws.Cells(i, 14).Value 'batch
          Me.Cells(k, 9).Font.ColorIndex = 5
        End If
        k = k + 1
        Next i
       
         
        Call addcheckboxes("4", k - 1)
             Application.ScreenUpdating = True
            
             If wsCaller <> "" Then
            Application.Sheets(wsCaller).Activate
            wsCaller = "none"
             End If
             
    End Sub
    
    
    Public Sub addcheckboxes(ByVal Lower As String, ByVal Upper As String)
    Dim ws As Worksheet
    
    
    Set ws = Workbooks("nursery.xls").Worksheets("Seeding")
      Application.ScreenUpdating = False
     ws.CheckBoxes.Delete
        ckbox = Lower
        ' add a checkbox for printing
      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
    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...???
    Last edited by onyxnz; 09-20-2011 at 12:14 AM. Reason: Solved

  2. #2
    Registered User
    Join Date
    09-19-2011
    Location
    NZ
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: Checkbox visibility setting fails

    [SOLVED] Problem in the creation, resulting in duplicates of checkboxes stacking on top of each other
    New code:

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1