Creating an array of checkbox names

Question

I'm having some trouble with creating an array of activex checkbox names. I want to create this array so I can use a For loop instead of having to type each checkbox code out separately. Here is what part of my code looks like. The erro I'm getting says type mismatch and highlights the &. The way this would work out I want array(0) = ThirteenJan, array(1) = ThirteenFeb and so on.

Dim Month(0 To 11) As String
Dim Year(0 To 3) As String
Dim Time(0 To 47) As CheckBox
Dim i, j, k, l, m As Integer

'The initial values of the months were named such because that is how the buttons have been named 'Initial Values Month(0) = "Jan" Month(1) = "Feb" Month(2) = "Mar" Month(3) = "Apr" Month(4) = "May" Month(5) = "Jun" Month(6) = "Jul" Month(7) = "Aug" Month(8) = "Sep" Month(9) = "Oct" Month(10) = "Nov" Month(11) = "Dec"

Year(0) = "Thirteen"
Year(1) = "Fourteen"
Year(2) = "Fifteen"
Year(3) = "Sixteen"

k = 0

'I can't get the following code to work and I'm not sure what's wrong with it. It says     type mismatch and highlights the &.
'Create an array that has all the names of the checkboxes in each element of it
For i = 0 To 3
    For j = 0 To 11
        Set Time(k) = Year(i) & Month(j)
    k = k + 1
    Next j
    k = k + 1
Next i

k = 4
l = 18

For i = LBound(Time) To UBound(Time)
    'j loops through worksheets, the summary sheets are organized differently than the     rest of the workbook so they have to have their own code
    For j = 2 To 3
        'k loops through the columns, 54 is Column BB
        If k = 16 Or k = 29 Or k = 42 Then
        k = k + 1
        End If
        If Time(i).Value = True Then
            Sheets(j).Columns(k).EntireColumn.Hidden = False
        Else
            Sheets(j).Columns(k).EntireColumn.Hidden = True
        End If
    Next j
    For j = 4 To 9
        If l = 30 Or l = 31 Or l = 44 Or l = 45 Or l = 58 Or l = 59 Then
            l = l + 1
        End If

Month() is a built-in function. Can you try renaming the array to Months()? (And Year() to Years().) — Chel
the same remark regard Time variable you declared- change it into Times or other. — Kazimierz Jawor
Thanks so much guys for catching those, my code is still not working but those were definitely some mistakes. The error I'm getting now is It says type mismatch and highlights the &. Set Times(k) = Years(i) & Months(j) — John Miko

Gary's Student Gary's Student · Accepted Answer · 2013-06-17T18:33:48

Sub Macro1()
        Dim MyCheckboxes(1 To 10) As OLEObject
        Dim shp As Shape
        Range("A1").Select
        For i = 1 To 10
            ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
                DisplayAsIcon:=False, Left:=240, Top:=75.75, Width:=49.5, Height:= _
                17.25).Select
            Set MyCheckboxes(i) = Selection
            Set shp = ActiveSheet.Shapes("CheckBox" & i)
            shp.Left = ActiveCell.Left
            shp.Top = ActiveCell.Top
            shp.Height = ActiveCell.Height
            shp.Width = ActiveCell.Width
            ActiveCell.Offset(2, 0).Select
        Next
    End Sub

Creating an array of checkbox names

1 Answers