CODEWITHSUNDEEP
excelvbalabelruntimetransparent
Yin Cognyto
Yin Cognyto

Reputation: 53

How do I make an Excel ActiveX label to be transparent ... at runtime?

I want to put a transparent label on top of a sheet in Excel, so that I can take advantage of the MouseMove event of the label to "draw cells" (aka change their fill color and so on) by mouse click / drag / etc. - since I can't do that on the cells per se.

Now everything works just fine, except that I can't make the label transparent at runtime (aka in VBA) ... while by doing exactly the same thing in Design Mode works as expected. Specifically, I have the code (more or less):

Dim MapLabel As OLEObject
On Error Resume Next
Sheet2.OLEObjects("MapLabel").Delete
Set MapLabel = Sheet2.OLEObjects.Add("Forms.Label.1")
MapLabel.name = "MapLabel"
MapLabel.Placement = xlMoveAndSize
MapLabel.Object.Caption = ""
' Problem line below
MapLabel.Object.BackStyle = fmBackStyleTransparent
' Problem line above
MapLabel.Left = Sheet2.cells(2, 6).Left
MapLabel.Top = Sheet2.cells(2, 6).Top
MapLabel.Width = Sheet2.cells(2,6).Width * 10
MapLabel.Height = Sheet2.cells(2,6).Height * 10

So, in words, I first delete the label named 'MapLabel', then recreate it (the above code goes into a "init" Sub). All the code lines except the one marked produce the desired result. The marked one does set the BackStyle property of the label to fmBackStyleTransparent ... but it doesn't actually make the label transparent. This is frustrating, because it's the same approach that works flawlessly at design time!

Do you have a solution to this? I read about solving similar problems by declaring the label as MsForms.Label or as Control, but the sheet object doesn't have those properties, plus, there are far more label properties which can be set using the OLEObject than with the help of MsForms.Label or Control.

Upvotes: 2

Views: 5760

Answers (2)

IR_IR
IR_IR

Reputation: 184

All you need to do after this line:

MapLabel.Object.BackStyle = fmBackStyleTransparent

put this line:

ActiveSheet.Shapes(MapLabel.Name).Fill.Transparency = 1

I hope I helped.

P.S. If you need explanation i will edit my answer.

Upvotes: 2

Bastien
Bastien

Reputation: 1

I had the same problem as you but in Word. The solution for me was to do the following:

In design mode:

  1. Right click on the object
  2. Navigate to Switch to automatic form/Image > Wrapping > In front of the text
  3. Add an empty picture to your label

Upvotes: -1

Related Questions