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...???
Bookmarks