Dynamically Generating Controls At Runtime Issue

[Kaah]

Hi all,

Has anyone tried to dynamically generate activex controls straight onto an excel sheet? I have been messing about with it and am almost there with it but I at present have one last issue to sort out. This is the important code so far. It loops through an array and for each array cell makes a new text label, positions it, names it, captions it, colours it, etc....

'**********************************************************

'\\paste into code window to read properly- sorry bout the mess here!
For iCount = LBound(vSetMemberArray) To UBound(vSetMemberArray)
iButtonCount = iButtonCount + 1
iTopPosition = iTopPosition + 30
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects.Add ClassType:="Forms.Label.1", Link:=False, _
DisplayAsIcon:=False, Left:=iLeftPosition, Top:=iTopPosition, Width:=132.75, Height _
:=21
sMembername = DescriptionToName("Entity", vSetMemberArray(iCount))
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iButtonCount).Name = "lbl" & sMembername
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iButtonCount).Object.Caption = vSetMemberArray(iCount)
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iButtonCount).Object.Font.Name = "Century Schoolbook"
' ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iButtonCount).Object.Font.Italic = True
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iButtonCount).Object.Font.Size = 8
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iButtonCount).Object.TextAlign = 2
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iButtonCount).Object.BackStyle = fmBackStyleTransparent
ThisWorkbook.Worksheets("xlsMainMenu&quot.OLEObjects(iCount + 2).Object.BackColor = 1000

'\\insert code to run buttons
'ThisWorkbook.VBProject.VBComponents.Item("xlsMainMenu&quot.CodeModule.AddFromString "private sub lbl" & sMemberName & "_click()" & vbCrLf & "msgbox " & sMemberName & vbCrLf & "end sub" & vbCrLf & vbCrLf

Next iCount

'**********************************************************


Everything works fine except for the transparent background!

when I go to the sheet to see how it worked all the buttons and stuff are there but the backgrounds are not transparent. If I look at the properties for any of the controls, it says that they should be transparent but they quiet clearly are not. If I manually change the transparent property it still doesn't change! Of course if I create a text label manually then the transparent property works well...this is really giving me the dirts!...

does anyone have any ideas? t'would help a lot! Email if you want the full code but it really wouldn't do you any good because it accesses a specific database tool that i use to fill the array.

BTW: the last line in the loop above dynamically generates the code to run the labels like buttons if you're interested...hope you guys find a use for that! msgbox command there just for testing.

thanks for any replies that I get.

k.

 

[Mossoft]

ThisWorkbook.Worksheets("xlsMainMenu&quot.oleobjects(iButtonCount).interior.colorindex = xlcolorindexnone

seems to work.

M

 

[Kaah]

Mos,

Thanks dude, that did the trick, its all looking good now...

regards

k.