Transparency Discrepency

[Quahaug]

There's a discrepency that I can't figure out. When I create a
commandbutton at design time and change the BackStyle property to
fmBackStyleTransparent, the underlying excel cell shows through.
However, when I do the same thing at run time, the underlying excel
cell does not show through. What is the difference and how can I
correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) = Worksheets(PredictSheet).OLEObjects. _
Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug

 

[Peter T]

I've never been able to make OLEObjects permanently transparent, either
manually (via properties) or programmatically.

The object might appear transparent after setting .BackStyle =
fmBackStyleTransparent but it reverts to opaque as soon as clicked with
mouse and/or various other actions.

Regards,
Peter T

 

[NickHK]

Peter,
I've always found these settings flakey. Slightly improved now, but..

XL2002:
If you set TakeFocusOnClick=false, the cmdbutton reverts to transparent when
the mouse is moved off the control, but you still get opaque between click
and Mouse_Off.
With TakeFocusOnClick=true, reverts to transparent when new cell selected.

XL2000:
Stays opaque after click, whatever settings used, as far as I can tell.

NickHK

I've never been able to make OLEObjects permanently transparent, either
manually (via properties) or programmatically.

The object might appear transparent after setting .BackStyle =
fmBackStyleTransparent but it reverts to opaque as soon as clicked with
mouse and/or various other actions.

Regards,
Peter T

There's a discrepency that I can't figure out. When I create a
commandbutton at design time and change the BackStyle property to
fmBackStyleTransparent, the underlying excel cell shows through.
However, when I do the same thing at run time, the underlying excel
cell does not show through. What is the difference and how can I
correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) = Worksheets(PredictSheet).OLEObjects. _
Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug

 

[Peter T]

Hi Nick,

I mainly use XL2000 and hadn't noticed the improvements in XL2002, thanks
for your comments.

In XL97/2000 there are a few kludgy ways to temporarily restore transparency
until the next mouse click. Eg paste-special entire-sheet.cells formats to
another sheet, sometimes merely selecting cells containing controls. Curious
as to why that should reset but not worthwhile as not permanent and doesn't
work at all with certain control types.

Regards,
Peter T

Peter,
I've always found these settings flakey. Slightly improved now, but..

XL2002:
If you set TakeFocusOnClick=false, the cmdbutton reverts to transparent when
the mouse is moved off the control, but you still get opaque between click
and Mouse_Off.
With TakeFocusOnClick=true, reverts to transparent when new cell selected.

XL2000:
Stays opaque after click, whatever settings used, as far as I can tell.

NickHK

I've never been able to make OLEObjects permanently transparent, either
manually (via properties) or programmatically.

The object might appear transparent after setting .BackStyle =
fmBackStyleTransparent but it reverts to opaque as soon as clicked with
mouse and/or various other actions.

Regards,
Peter T

There's a discrepency that I can't figure out. When I create a
commandbutton at design time and change the BackStyle property to
fmBackStyleTransparent, the underlying excel cell shows through.
However, when I do the same thing at run time, the underlying excel
cell does not show through. What is the difference and how can I
correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) = Worksheets(PredictSheet).OLEObjects. _
Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug

 

[NickHK]

Personally, I would avoid trying to use transparency in these situations, as
the reliability just is not there.

NickHK

Hi Nick,

I mainly use XL2000 and hadn't noticed the improvements in XL2002, thanks
for your comments.

In XL97/2000 there are a few kludgy ways to temporarily restore transparency
until the next mouse click. Eg paste-special entire-sheet.cells formats to
another sheet, sometimes merely selecting cells containing controls. Curious
as to why that should reset but not worthwhile as not permanent and doesn't
work at all with certain control types.

Regards,
Peter T

Peter,
I've always found these settings flakey. Slightly improved now, but..

XL2002:
If you set TakeFocusOnClick=false, the cmdbutton reverts to transparent when
the mouse is moved off the control, but you still get opaque between click
and Mouse_Off.
With TakeFocusOnClick=true, reverts to transparent when new cell selected.

XL2000:
Stays opaque after click, whatever settings used, as far as I can tell.

NickHK

I've never been able to make OLEObjects permanently transparent, either
manually (via properties) or programmatically.

The object might appear transparent after setting .BackStyle =
fmBackStyleTransparent but it reverts to opaque as soon as clicked with
mouse and/or various other actions.

Regards,
Peter T

There's a discrepency that I can't figure out. When I create a
commandbutton at design time and change the BackStyle property to
fmBackStyleTransparent, the underlying excel cell shows through.
However, when I do the same thing at run time, the underlying excel
cell does not show through. What is the difference and how can I
correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) = Worksheets(PredictSheet).OLEObjects. _
Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug

 

[NickHK]

Peter,
Not sure if it's related, but XL2002 has lost the ability to make a colour
on a graphic transparent. This worked in XL2000, but now has no effect in
XL2002:

ActiveSheet.Pictures.Insert("D:\Signature.jpg").Select
Selection.ShapeRange.PictureFormat.TransparentBackground = msoTrue
Selection.ShapeRange.PictureFormat.TransparencyColor = RGB(255, 255,
255)
Selection.ShapeRange.Fill.Visible = msoFalse

Manually same results.

NickHK

Hi Nick,

I mainly use XL2000 and hadn't noticed the improvements in XL2002, thanks
for your comments.

In XL97/2000 there are a few kludgy ways to temporarily restore transparency
until the next mouse click. Eg paste-special entire-sheet.cells formats to
another sheet, sometimes merely selecting cells containing controls. Curious
as to why that should reset but not worthwhile as not permanent and doesn't
work at all with certain control types.

Regards,
Peter T

Peter,
I've always found these settings flakey. Slightly improved now, but..

XL2002:
If you set TakeFocusOnClick=false, the cmdbutton reverts to transparent when
the mouse is moved off the control, but you still get opaque between click
and Mouse_Off.
With TakeFocusOnClick=true, reverts to transparent when new cell selected.

XL2000:
Stays opaque after click, whatever settings used, as far as I can tell.

NickHK

I've never been able to make OLEObjects permanently transparent, either
manually (via properties) or programmatically.

The object might appear transparent after setting .BackStyle =
fmBackStyleTransparent but it reverts to opaque as soon as clicked with
mouse and/or various other actions.

Regards,
Peter T

There's a discrepency that I can't figure out. When I create a
commandbutton at design time and change the BackStyle property to
fmBackStyleTransparent, the underlying excel cell shows through.
However, when I do the same thing at run time, the underlying excel
cell does not show through. What is the difference and how can I
correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) = Worksheets(PredictSheet).OLEObjects. _
Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug

 

[Peter T]

Hi Nick,

In XL2000 it's only possible to set TransparentBackground &
TransparencyColor with a BMP (AFAIK). I notice you are working with a JPG so
I wouldn't expect what you are doing to work in any version.

Images that were saved with a transparent mask normally load by default with
the mask transparent and .Fill.Visible false. Changing TransparentBackground
& TransparencyColor would do nothing though the .Fill.Visible property can
be toggled to see through the mask or not (actually not all image types).

I don't think JPG's can have a transparent mask (I could be wrong) so it
might mean converting your JPG to say a GIF and setting the mask in an image
processor. Alternatively of course convert the JPG to a BMP then use your
code.

Or are you saying your code works with the exact same JPG in XL2000, if so
I'd be perplexed but happy to stand corrected.

I don't think the above is related to the problem of 'persisting' worksheet
controls transparent property. Unlike images these are drawn as vectors. As
you said in your previous post best not to try, instead simulate controls
with shapes.

Regards,
Peter T

Peter,
Not sure if it's related, but XL2002 has lost the ability to make a colour
on a graphic transparent. This worked in XL2000, but now has no effect in
XL2002:

ActiveSheet.Pictures.Insert("D:\Signature.jpg").Select
Selection.ShapeRange.PictureFormat.TransparentBackground = msoTrue
Selection.ShapeRange.PictureFormat.TransparencyColor = RGB(255, 255,
255)
Selection.ShapeRange.Fill.Visible = msoFalse

Manually same results.

NickHK

Hi Nick,

I mainly use XL2000 and hadn't noticed the improvements in XL2002, thanks
for your comments.

In XL97/2000 there are a few kludgy ways to temporarily restore transparency
until the next mouse click. Eg paste-special entire-sheet.cells formats to
another sheet, sometimes merely selecting cells containing controls. Curious
as to why that should reset but not worthwhile as not permanent and doesn't
work at all with certain control types.

Regards,
Peter T

Peter,
I've always found these settings flakey. Slightly improved now, but..

XL2002:
If you set TakeFocusOnClick=false, the cmdbutton reverts to

transparent
when

the mouse is moved off the control, but you still get opaque between click
and Mouse_Off.
With TakeFocusOnClick=true, reverts to transparent when new cell selected.

XL2000:
Stays opaque after click, whatever settings used, as far as I can tell.

NickHK

I've never been able to make OLEObjects permanently transparent, either
manually (via properties) or programmatically.

The object might appear transparent after setting .BackStyle =
fmBackStyleTransparent but it reverts to opaque as soon as clicked with
mouse and/or various other actions.

Regards,
Peter T

There's a discrepency that I can't figure out. When I create a
commandbutton at design time and change the BackStyle property to
fmBackStyleTransparent, the underlying excel cell shows through.
However, when I do the same thing at run time, the underlying excel
cell does not show through. What is the difference and how can I
correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) =

Worksheets(PredictSheet).OLEObjects.

_

Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug

 

[NickHK]

Peter,
Works for me with .jpeg. But I would assume a jpeg is converted to a bmp
before it is rendered on the WS anyway.
After a bit more testing, seems to be related to colour mode of the original
jpeg. Creating 2 identical jpgs, apart from colour mode, with PhotoShop :

XL2000:
Handle True colour RGB and GreyScale

XL2002:
Handle True colour RGB. Fails on GreyScale

The code I included was generated in XL2002 when manually using the Pictures
toolbar to set a transparent colour on a graphic.
It's not vital, but I can't now insert a signature graphic to fake a sig on
top of a stamp. Unless I resample them in true colour RGB.

Colour mode does not seem to be a factor for .gif and .bmp in either
version.

Just my luck..need to play with the picture format and the colour mode to
get desired results I guess.

NickHK

Hi Nick,

In XL2000 it's only possible to set TransparentBackground &
TransparencyColor with a BMP (AFAIK). I notice you are working with a JPG so
I wouldn't expect what you are doing to work in any version.

Images that were saved with a transparent mask normally load by default with
the mask transparent and .Fill.Visible false. Changing TransparentBackground
& TransparencyColor would do nothing though the .Fill.Visible property can
be toggled to see through the mask or not (actually not all image types).

I don't think JPG's can have a transparent mask (I could be wrong) so it
might mean converting your JPG to say a GIF and setting the mask in an image
processor. Alternatively of course convert the JPG to a BMP then use your
code.

Or are you saying your code works with the exact same JPG in XL2000, if so
I'd be perplexed but happy to stand corrected.

I don't think the above is related to the problem of 'persisting' worksheet
controls transparent property. Unlike images these are drawn as vectors. As
you said in your previous post best not to try, instead simulate controls
with shapes.

Regards,
Peter T

Peter,
Not sure if it's related, but XL2002 has lost the ability to make a colour
on a graphic transparent. This worked in XL2000, but now has no effect in
XL2002:

ActiveSheet.Pictures.Insert("D:\Signature.jpg").Select
Selection.ShapeRange.PictureFormat.TransparentBackground = msoTrue
Selection.ShapeRange.PictureFormat.TransparencyColor = RGB(255, 255,
255)
Selection.ShapeRange.Fill.Visible = msoFalse

Manually same results.

NickHK

formats

to

another sheet, sometimes merely selecting cells containing controls. Curious
as to why that should reset but not worthwhile as not permanent and doesn't
work at all with certain control types.

Regards,
Peter T


Peter,
I've always found these settings flakey. Slightly improved now, but..

XL2002:
If you set TakeFocusOnClick=false, the cmdbutton reverts to transparent
when
the mouse is moved off the control, but you still get opaque between click
and Mouse_Off.
With TakeFocusOnClick=true, reverts to transparent when new cell selected.

XL2000:
Stays opaque after click, whatever settings used, as far as I can tell.

NickHK

I've never been able to make OLEObjects permanently transparent, either
manually (via properties) or programmatically.

The object might appear transparent after setting .BackStyle =
fmBackStyleTransparent but it reverts to opaque as soon as clicked with
mouse and/or various other actions.

Regards,
Peter T

There's a discrepency that I can't figure out. When I create a
commandbutton at design time and change the BackStyle property to
fmBackStyleTransparent, the underlying excel cell shows through.
However, when I do the same thing at run time, the underlying excel
cell does not show through. What is the difference and how can I
correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) =

Worksheets(PredictSheet).OLEObjects.

_
Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug

 

[Peter T]

Hi again

Interesting and I stand corrected, at least partially.

All the JPG's & GIF's I tried previously failed in XL2000 (to set
TransparentBackground). However just tried a 256 colour JPG and that does
indeed work.

Regards,
Peter T

Peter,
Works for me with .jpeg. But I would assume a jpeg is converted to a bmp
before it is rendered on the WS anyway.
After a bit more testing, seems to be related to colour mode of the original
jpeg. Creating 2 identical jpgs, apart from colour mode, with PhotoShop :

XL2000:
Handle True colour RGB and GreyScale

XL2002:
Handle True colour RGB. Fails on GreyScale

The code I included was generated in XL2002 when manually using the Pictures
toolbar to set a transparent colour on a graphic.
It's not vital, but I can't now insert a signature graphic to fake a sig on
top of a stamp. Unless I resample them in true colour RGB.

Colour mode does not seem to be a factor for .gif and .bmp in either
version.

Just my luck..need to play with the picture format and the colour mode to
get desired results I guess.

NickHK

Hi Nick,

In XL2000 it's only possible to set TransparentBackground &
TransparencyColor with a BMP (AFAIK). I notice you are working with a

JPG

so
I wouldn't expect what you are doing to work in any version.

Images that were saved with a transparent mask normally load by default with
the mask transparent and .Fill.Visible false. Changing TransparentBackground
& TransparencyColor would do nothing though the .Fill.Visible property can
be toggled to see through the mask or not (actually not all image types).

I don't think JPG's can have a transparent mask (I could be wrong) so it
might mean converting your JPG to say a GIF and setting the mask in an image
processor. Alternatively of course convert the JPG to a BMP then use your
code.

Or are you saying your code works with the exact same JPG in XL2000, if so
I'd be perplexed but happy to stand corrected.

I don't think the above is related to the problem of 'persisting' worksheet
controls transparent property. Unlike images these are drawn as vectors. As
you said in your previous post best not to try, instead simulate controls
with shapes.

Regards,
Peter T

formats

can

I

correct it to get the underlying excel cells to show through with
runtime creation of commandbuttons?

Here's a piece of my runtime code:

[snip]

Dim TeamCB(1 To 2, 1 To 32) As OLEObject

[snip]

Set TeamCB(1, I) = Worksheets(PredictSheet).OLEObjects.
_
Add("forms.CommandButton.1")

With TeamCB(1, I)
.Object.BackColor = &HFFFFFF
.Object.AutoSize = False
.Object.BackStyle = fmBackStyleTransparent
.Height = TeamCBHeight
.Width = TeamCBWidth
.Top = StPopTopBase + (StPopTopSize * I)
.Left = StPopVPLeft
.Object.Caption = ""
End With

[snip]

Thanks for any and all help you can give me.

Quahaug