Results 1 to 4 of 4

Thread: [RESOLVED] Containing bunch of checkboxes into an array - errors

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    2

    Resolved [RESOLVED] Containing bunch of checkboxes into an array - errors

    Hello,

    I have tried to create an array of option buttons (for example Checkboxes). With a loop or calling a function I'd like to call ALL or just selected buttons from array and change their falue (TRUE/FALSE).

    Here is the way I tried to accomplish it, but I continually receive errors (Error 13, then other errors as I change tiny bit of code...):

    Dim Buttons(5) As Checkbox

    Public Function InitializeButtons(ButtonArray() As Checkbox)
    Set ButtonArray(0) = TableForm.Checkbox1
    Set ButtonArray(1) = TableForm.Checkbox2
    Set ButtonArray(2) = TableForm.Checkbox3
    Set ButtonArray(3) = TableForm.Checkbox4
    Set ButtonArray(4) = TableForm.Checkbox5
    End Function

    Private Sub Workbook_Open()
    void = InitializeButtons(Buttons)
    End Sub

    I would like to add in the next lines after the initialization the value changing code.


    Example of code with a loop:

    Public Sub LoadLots(sName As String, streamLots() As String)
    Dim btn As MSForms.Checkbox
    Label1.Caption = sName
    For o = 1 To 9
    Set btn = Me.Controls("Checkbox" & o)
    If streamLots(o) <> "" Then
    btn.Value = True
    Else
    btn.Value = False
    End If
    Next
    End Sub

    None of these work...

    Could you please suggest me learning material or an article so I can understand this field of VBA better?

    Thank you in advance,

    Nidjo

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: Containing bunch of checkboxes into an array - errors

    the proper way to work with multiple controls, if you want the same event to fire for each control, is to use a class then set each control to an instance of the class and add each instance of the class to a collection, there are examples of doing like this in this forum, if need be i can probably find some some example for you

    having said that it does not appear that you actually need to use the above method for what you are trying to achieve
    in the first code, i assume that it is just a typo in the post that your array is called buttons and you are filling elements of buttonarray? f you had option explicit at the top of your module you would get an error as soon as you try to run the code as buttonarray would be undimensioned and cause a different error, something like procedure not found (or defined)

    error 13 is type mismatch, which in this case is probably because msforms checkboxes are not regular checkboxes, but are of a different type, as you have in the second code
    try
    Code:
    Dim ButtonArray(5) As MSForms.CheckBox
    also for 5 controls you only need an array of 4 (as in short for 0 to 4) is 5 elements, or you can dimension your array as (1 To 5), so the array index always aligns with the numeric in the name

    this modification of your second code works without error, just toggles the buttons
    Code:
    Dim btn As MSForms.CheckBox
    'Label1.Caption = sName
    For o = 1 To 3
        Set btn = Me.Controls("Checkbox" & o)
        btn.Value = Not btn.Value
        'If streamLots(o) <> "" Then
        'btn.Value = True
        'Else
        'btn.Value = False
        'End If
    Next
    i only put 3 checkboxes on a form and no label, in fact you do not need the btn object at all, as Controls("Checkbox" & o) = Not Controls("Checkbox" & o) would work just as well, putting the value property has merit, but will work without
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    New Member
    Join Date
    Oct 2020
    Posts
    2

    Re: Containing bunch of checkboxes into an array - errors

    Thank you westconn1 for your quick answer.

    Yes it was universally stated as Buttons, I tried multiple combinations with different option buttons, checkboxes etc.

    I have declared "o" as long and it works like a clock.

    I wasn't familiar with this method before so I am very grateful for your help!
    There are so many things in VBA, its just so much complicated ughh...


    Trying to find the method to use classes and collections for arrays, if you have it saved or bookmarked somewhere, you can send it for sure . If not, I will find it eventually!

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    Re: [RESOLVED] Containing bunch of checkboxes into an array - errors

    Trying to find the method to use classes and collections for arrays,
    have a look at
    http://www.siddharthrout.com/index.p...ontrol-arrays/
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width