-
Oct 9th, 2020, 01:23 PM
#1
Thread Starter
New Member
[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
-
Oct 10th, 2020, 03:07 AM
#2
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
-
Oct 12th, 2020, 09:12 AM
#3
Thread Starter
New Member
-
Oct 13th, 2020, 04:40 AM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|