control arrays excel 2003

[sbnktn]

i use excel 2003. I have written following code in 'this workbook' as it has to run when the workbook is opened.

Dim ctrlarray(1 To 5) As Excel.OLEObject
Dim i As Integer
For i = 1 To 5
Set ctrlarray(i) = ActiveSheet.OLEObjects.Add("Commandbutton1")
Next i

but for some reason it gives error on line with 'Set'. I need help to set this right.
Thanks!

 

[RoryA]

The class type is wrong - try:

Set ctrlarray(i) = ActiveSheet.OLEObjects.Add("Forms.Commandbutton.1")

 

[sbnktn]

thanks for the response. its working but adds only one command button wherein i require five. what might be wrong?

 

[RoryA]

I think you'll find you have 5 - they just happen to be positioned on top of one another because you didn't specify where they were to go.

 

[sbnktn]

you may be right. let me reposition and update,thanks!

 

[sbnktn]

can i reposition the buttons within the array and also give different names. I tried formatting using with....statement within the loop. but it was not helpful. how do i write the code for different left, top position and name being different for these 5 buttons. hope i am not asking more !!

 

[RoryA]

You can use something like this, for example:

Dim ctrlarray(1 To 5) As Excel.OLEObject
Dim i As Integer
For i = 1 To 5
   Set ctrlarray(i) = ActiveSheet.OLEObjects.Add(classtype:="Forms.Commandbutton.1", Left:=0, Top:=(i - 1) * 25, Width:=50, Height:=25)
   ctrlarray(i).Name = "Button" & i
Next i

 

[sbnktn]

hey that really works! can u explain me the details used in Top:= (i-1)*25. i understand 25 is the space between each boxes right! how to specify the space from the top to the first button?

 

[RoryA]

For the first button, i = 1 so (i-1)*25 = 0 * 25
If you want to move them all down, simply add a constant value - for example:

Top:= (i-1)*25 + 50

 

[sbnktn]

thanks a ton! that really worked. I would like to correct myself when i told about the different names i ctrlarray(i).Name = "Button" & i. i meant working with captions here. I think i have to use text variable here right!