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]

You should be able to use:

ctrlarray(i).Caption = "Button" & i

 

[sbnktn]

...well i think i didnt put things right before. I would like to use an array to give different captions to these 5 command buttons already added. also how to go about with the formatting of command button as to the font, font size, shadow etc at runtime after being added. thanks.

 

[RoryA]

For example:

with ctrlarray(i)
.Caption = somearray(i)
.font.name = "Tahoma"
' add more properties here
end with

 

[sbnktn]

will the whole set of code look this way....i am sure there is something wrong here with 'mycaptions'. because i dont understand how to set the data for this array.

Dim ctrlarray(1 To 5) As Excel.OLEObject
Dim i As Single
Dim mycaptions(1 To 5) As String
For i = 1 To 5
Set ctrlarray(i) = ActiveSheet.OLEObjects.Add("Forms.Commandbutton.1", Left:=126.75, Height:=32.25, Width:=213.75, Top:=(i - 1) * 50 + 150)
    With ctrlarray(i)
        .Caption = mycaptions(i)
        .Font.Name = "Tahoma"
End With
ctrlarray(i).Caption = "button" & i
Next i

 

[RoryA]

You need code like:

mycaptions(1) = "some text"
mycaptions(2) = "some other text"

and so on

 

[sbnktn]

Dim ctrlarray(1 To 5) As Excel.OLEObject
Dim i As Single
Dim mycaptions(1 To 5) As String
For i = 1 To 5
mycaptions(1) = "LoP Reports"
mycaptions(2) = "Export Reports"
mycaptions(3) = "Manpower Reports"
mycaptions(4) = "Investment Reports"
mycaptions(5) = "Other Reports"
Set ctrlarray(i) = ActiveSheet.OLEObjects.Add("Forms.Commandbutton.1", Left:=120, Height:=32.25, Width:=213.75, Top:=(i - 1) * 50 + 150)
    With ctrlarray(i)
        .Caption = mycaptions(i)
        .Font.Name = "Tahoma"
End With
Next i

i have put it this way...but i am not getting the required result.

 

[RoryA]

My fault - you need the Object property of the OLEObject

   Dim ctrlarray(1 To 5)         As Excel.OLEObject
   Dim i                         As Long
   Dim mycaptions(1 To 5)        As String
   
   mycaptions(1) = "LoP Reports"
   mycaptions(2) = "Export Reports"
   mycaptions(3) = "Manpower Reports"
   mycaptions(4) = "Investment Reports"
   mycaptions(5) = "Other Reports"
   For i = 1 To 5
      Set ctrlarray(i) = ActiveSheet.OLEObjects.Add("Forms.Commandbutton.1", Left:=120, Height:=32.25, Width:=213.75, Top:=(i - 1) * 50 + 150)
       With ctrlarray(i).Object
           .Caption = mycaptions(i)
           .Font.Name = "Tahoma"
      End With
   Next i

 

[sbnktn]

thnx again..its worked perfectly! though my need is served...just for the curiosity to know..what if my control array was more than 5 say 100 or so. do i need to set the caption for each command button individually. is it not possible in excel vba to set the data for the captions under one variable. and then link then with the index number to show on the command button?
also where do i click to show the query is resolved.

 

[RoryA]

You could use a range to store the captions and then load that into an array - note it will be 2 dimensional:

   Dim ctrlarray(1 To 5)         As Excel.OLEObject
   Dim i                         As Long
   Dim mycaptions
   mycaptions = Range("A1:A100").value

   For i = 1 To 100
      Set ctrlarray(i) = ActiveSheet.OLEObjects.Add("Forms.Commandbutton.1", Left:=120, Height:=32.25, Width:=213.75, Top:=(i - 1) * 50 + 150)
       With ctrlarray(i).Object
           .Caption = mycaptions(i, 1) ' note you have to specify the second dimension here
           .Font.Name = "Tahoma"
      End With
   Next i

We don't mark threads 'Solved' here so you don't need to do anything.

 

[sbnktn]

that was a nice piece of info. I would like to try this! thanks again for the support!