current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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
share|improve this question
1  
Month() is a built-in function. Can you try renaming the array to Months()? (And Year() to Years().) –  rdhs Jun 17 '13 at 15:55
    
the same remark regard Time variable you declared- change it into Times or other. –  KazJaw Jun 17 '13 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 Jun 17 '13 at 16:19

1 Answer 1

up vote 0 down vote 
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
share|improve this answer
    
Hey @Gary's Student thanks for helping me out but could you explain what your code does please my knowledge of vba is limited –  John Miko Jun 17 '13 at 19:01
    
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 –  Gary's Student Jun 17 '13 at 21:43
    
Thank you Gary's Student! –  John Miko Jun 20 '13 at 19:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.