ActiveX Label Transparency driving me insane, fmbackstyle does not work.

[Mike Hoff]

Hi

I've been at it for many hours trying to figure out why I cannot change the backstyle property on an activex label to transparent when I add the label programmatically.

I have attached a simple workbook that shows this problem. Click the button and a label will be added. Then go into developer/design and change the backstyle to transparent. Notice how it ignores this and the label remains white.

Now manually add a label and do the same thing, go into properties and change it to transparent. Works now.

I have a very huge application that I am needing many of these labels (much smaller) which I use for mousemove detection. It is not practical to set the transparency by hand and added these by hand. Because transparency change does not work in design mode it also does not work in vba programmatic mode which is ultimately what I am after. Unless I am totally missing the boat here, can anyone shed some light on this?

Thanks

 

[Hui]

No file attached

 

[Hui]

Isn't it as simple as

CommandButton1.BackStyle = fmBackStyleTransparent

 

[Mike Hoff]

That proves it, staring at the computer way to long today. Here is the file.

 

[Hui]

But I think you'd be better to use Shapes rather than ActiveX label controls
Add a Shape to the worksheet
Rename it "myShp1"
Copy and place where you want

then use code like:

Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
  If Left(Shp.Name, 5) = "myShp" Then Shp.Fill.Visible = msoFalse
Next Shp

 

[Mike Hoff]

It's supposed to be that simple, but it isn't turning out that way, and it is a label which I need to set to totally transparent so I can use mouse move event. Command button can't be made totally transparent as it still shows the button border.

 

[Mike Hoff]

how do I use the mousemove event for a shape? I thought you needed active X for that?

 

[Hui]

Can you explain what your trying to achieve ?

 

[Mike Hoff]

I have a three way switch using shapes, I only want this switch visible when I mouse over it. As there are dozens and dozens of these switches, having them all visible clutters up the app. They need to only be visible individually when the mouse moves over the correct spot for any given switch. So I want a label over the switch that is invisible, when I roll over it, the switch becomes visible. I'll have another larger label over that one as well one layer beneath so when I roll away, the switch becomes invisible again.

I need the activex label as it has the mouse move event, as far as I know the forms label does not. The user will never see these labels and the switches show up when the mouse moves over the area.

I can accomplish all of this if I set all the labels manually to fmBackstyleTransparent, but I wish to do it via code so If the switches ever break or are not in the correct position, I can issue a "rebuild switches command" and with some code reset all the switches.

Long story short, is this another of Excel's long lived bugs that has not been addressed since Office XL?

 

[Hui]

Have you considered using a different technique ?
I'd suggest: http://www.excelhero.com/blog/2011/03/excel-dynamic-checkmark.html

 

[Mike Hoff]

Nice examples, but not sure if it will work for my app. Attached is a small workbook that shows conceptually what I am working on for my app. The box around the switch is just there so you can find it. The app won't have that box as the switch will be located in obvious areas for the user.

The switch in the box was manually created and works as designed. However, if you click on create switch programmatically, you'll see the issue with the labels right away. Even if you select one of the labels and in the properties change it, there is no effect. As a matter of fact the properties will show it as transparent so it is executing my code, but not reacting to it.

I can only surmise it to be a bug, unless my vba code is incorrect, but if it were, then the label would not be set to transparent in the properties, as the default as you probably know is opaque.

 

[Hui]

Can you post the file where you want to use the switch ?
So we can look at alternatives