Creating an Array of Objects using VS Basic for Excel Macros

[ErantD]

How do I turn an Object name into an Array.
Example I ahave 10 checkboxes and I´d like to turn them into a array to use in a loop.
Note I am using Visaul Basic for EXCEL MACROS and simply copying the name or putting the name in the name type column can´t be done. How do I do this for Excel Macros???

say: (chkBox(#) being the name of the checkboxs)


Dim i as Integer
For i= 1 to 10
chkBox(i)= i
Next i

 

[dougcranston]

ErantD



Look at this site for "some info" on checkbox. About 2/3's down.

http://maths.sci.shu.ac.uk/units/20-2510/notes/controls.shtml

Go to Google and use "excel vba checkboxes as collection" as the search string.. One of the finds will be to a German site and it has a translate option "Herbers Excel-FAQ - Herbers Excel/VBA-FAQ - Version 5.2 "

Change the value/content of several UserForm-controls
This could be adapted to do what you are asking for:

http://www.erlandsendata.no/english/vba/dialogs/userformcontrols.htm

Ascertain the number of CheckBoxes in an UserForm

http://www.excel-center.com/mailing/069699h.htm

Hope this helps.
DougCranston

 

[ErantD]

Answering my own question with help from "dougcranston".

This is what I did
I gave my checkboxes a TAG with consecutive numbers, and placed my checkboxes in a frame.

Then I used the fallowing code=

Private Sub CommandButton1_Click()
Dim ctrl As Control
Dim x As Integer

For Each ctrl In UserForm1.Frame1.Controls
If TypeName(ctrl) = "CheckBox" Then
Range("A" & ctrl.Tag).FormulaR1C1 = ctrl.Value
End If
Next ctrl

End Sub

Thus I can store any results from my check boxes on a Excel sheet. (I was trying to make a Default Command buton to restore and save changes to a form.) Thus I the results can not change if I add a new text box or take one out.

Thanks again dougcranston

 

[dougcranston]

ErantD,

Great code. Short, sweet and gets the job done.

DougCranston