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
– Chel, Commented Jun 17, 2013 at 15:55
the same remark regard Time variable you declared- change it into Times or other. — Kazimierz Jawor
– Kazimierz Jawor, Commented Jun 17, 2013 at 16:15
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
– John Miko, Commented Jun 17, 2013 at 16:19

Gary's Student · Accepted Answer · 2013-06-17 18:43:15Z

0

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

edited Jun 17, 2013 at 18:43

answered Jun 17, 2013 at 18:33

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

John Miko Over a year ago

Hey @Gary's Student thanks for helping me out but could you explain what your code does please my knowledge of vba is limited

Gary's Student Over a year ago

Within the loop I enter a check-box; store the new check-box in an array; re-size and position the check-box by treating it as a Shape

Collectives™ on Stack Overflow

Creating an array of checkbox names

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related