1

as I try to create a check box array for for-loop using but I've got error 91, can any one can give me some suggestion to solve this? thx for help 

Sub checkBoxSet()

    Dim activeSheet As Worksheet
    Set activeSheet = ActiveWorkbook.activeSheet
    Dim control(1 To 5) As CheckBox
     control(1) = Sheet1.CheckBox1 'star error 91 here
     control(2) = Sheet1.CheckBox2
     control(3) = Sheet1.CheckBox3
     control(4) = Sheet1.CheckBox4
     control(5) = Sheet1.CheckBox5

    For i = LBound(control) To UBound(control)
    ' copy the old sheet checkbox to new sheet
        activeSheet.CheckBoxes.Add(control(i).Left, control(i).Top, control(i).Width, control(i).Height).Select
             With Selection
                .Caption = ""
                .Value = xlOff
                .LinkedCell = "C" & ToRow
                .Display3DShading = False
            End With

    Next i

End Sub

[update : 5/3/2016 23:05 HKT] pls accept me forgot to provide the background about this code, as here, it have two excel file, call temp.xlsm and month.xls, as the temp will copy a part of data to month.xls, also this part is about copy the checkbox to the month.xls, in temp.xlsm, it have 5 sheet, and the sheet1 is code name of the main sheet .

Improve this question
6
  • 1
    Need to use Set when assigning objects. Als you do not need to set ActiveSheet - there's only ever one of these per instance of Excel. Commented Mar 5, 2016 at 5:40
  • Hi tim, your suggestion i've try before, I try to put set for every control, but it got another error that around 10xx or15xx.etc (sorry that i'm in the street), is there any reason to make it can solve? Commented Mar 5, 2016 at 7:23
  • What type of checkboxes are they? Forms or ActiveX ? Commented Mar 5, 2016 at 8:05
  • hi tim, I don't know... may be ActiveX, as I got it on the developer bar... also, this form I'am not the owner... so that I don't know the detail Commented Mar 5, 2016 at 11:46
  • wait. what "form"? you didn't mention any form in your question. are those checkboxes in a sheet or in a form? Commented Mar 5, 2016 at 12:27

1 Answer 1

Reset to default
1

"Sheet1" must be set (some "ThisWorkBook.Sheets(1)" or"ThisWorkBook.Sheets(Sheet1)"?)

declare control() variable as of Shape type and then get chekboxes in the old sheet as items of its Shapes collection

something like this

Option Explicit

Sub checkBoxSet()
Dim i As Integer
Dim control(1 To 5) As Shape

With ThisWorkbook.Sheets("CheckBoxOldSheet")
    Set control(1) = .Shapes("CheckBox1")
    Set control(2) = .Shapes("CheckBox2")
    Set control(3) = .Shapes("CheckBox3")
    Set control(4) = .Shapes("CheckBox4")
    Set control(5) = .Shapes("CheckBox5")
End With

' copy the old sheet checkbox to new sheet
With activeSheet
    For i = LBound(control) To UBound(control)
        With .CheckBoxes.Add(control(i).Left, control(i).Top, control(i).Width, control(i).Height)
            .Caption = ""
            .Value = xlOff
'            .LinkedCell = "C" & ToRow
            .Display3DShading = False
        End With
    Next i
End With

End Sub

or you can make it more flexible like follows

Sub checkBoxSet2()
Dim shp As Variant

' copy the old sheet checkbox to new sheet
With activeSheet
    For Each shp In GetShapes(ThisWorkbook.Sheets("CheckBoxOldSheet"), "CheckBox")
        With .CheckBoxes.Add(shp.Left, shp.Top, shp.Width, shp.Height)
            .Caption = ""
            .Value = xlOff
    '            .LinkedCell = "C" & ToRow
            .Display3DShading = False
        End With
    Next shp
End With

End Sub


Function GetShapes(ws As Worksheet, shpNamePart As String) As Shape()
Dim foundShps() As Shape, shp As Shape
Dim nShp As Integer

With ws
    ReDim foundShps(1 To .Shapes.Count)
    For Each shp In .Shapes
        If shp.name Like shpNamePart & "*" Then ' <== adapt your name pattern to your needs
            nShp = nShp + 1
            Set foundShps(nShp) = shp
        End If
    Next shp
End With
ReDim Preserve foundShps(1 To nShp)

GetShapes = foundShps
End Function

where you get all shapes from a worksheet provided their name follows a pattern

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

7 Comments

"ThisWorkbook" is the workbook where the macro resides. Does it actually have a worksheet named "sheet1"?
year, I conform that
I tested it with no problems. Step through your code and use immediate window to query all relevant variables values when you hit the offending statement. for instance you could type "?ThisWorkbook.name" to see which workbook you'r referring to. and then "?ThisWorkbook.Sheets.Count" to see how many sheets it has and finally "?ThisWorkbook.Sheets(1).name" to see first worksheet name and then just change the index from 1 to the number you got previuosly
sorry, I forgot that this sub is in the module, will it make any error because not on the sheet?
the sub can stay in the module. do those other ckeckings I suggested and let me know. or explain in clear detail what's the background you're acting in (how many workbooks involved and its names, how many sheets has each of those workbooks and their names, which sheet do checkboxes reside in). or upload your project
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.