Programmatically add control to worksheet.

[LiamRogers]

Hi there.

I am creating a dash showing agent performance in a call center. Depending on what area they work in, they will have different numbers of KPI. I use a chart to display the KPI with transparent labels over the bars on the charts that are used to change the content of a second graph to that KPI.

I want to get vba to create the appropriate number of labels, rename them, resize them and make them transparent with no border.

I have trauled google for some reference code, but I can never seem to get it to do what I need it to do >/

The following will run fine, but does not do everything I need t to...

Sub Test()
Dim lbl As OLEObject
Set lbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")
With lbl
.Name = "Upgrades/IBC"
.Top = 72
.Left = 48
.Height = 192
.Width = 50
End With
End Sub

This is essentially what I need it to do for each new label...

Sub Test()
Dim lbl As OLEObject
Set lbl = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Label.1")
With lbl
.Name = "Upgrades/IBC"
.Top = 72
.Left = 48
.Height = 192
.Width = 50
.BackStyle = fmBackStyleTransparent
.BorderStyle = fmBorderStyleSingle
.Caption = ""
End With
End Sub

But it errors at .BackStyle = fmBackStyleTransparent with the following description 'Object doesn't support this property or method'.

So I think maybe it is not a forms label?..

I have tryed

Set dynamicControl = Me.Controls.AddControl(customControl, range1, "dynamic")

But I get 'Method or data member not found, it works in a form but not in a sheet. Am I missing a library perhaps?

I dont know, I am stuck. Hoping someone can help because this has held me up for ages now and I think my head is going to explode.

Many thanks,
Liam

 

[LiamRogers]

No one?

 

[LiamRogers]

bump

 

[LiamRogers]

Ok, so thanks anyway guys, I'll use a less good way to do this.

I'll update the thread when I find out what the issue is for the sake of the archive.