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

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

"Quahaug" wrote in message
ps.com...
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,
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

"Peter T" <peter_t@discussions wrote in message
...
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

"Quahaug" wrote in message
ps.com...
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

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


"NickHK" wrote in message
...
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

"Peter T" <peter_t@discussions wrote in message
...
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

"Quahaug" wrote in message
ps.com...
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

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

NickHK

"Peter T" <peter_t@discussions wrote in message
...
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


"NickHK" wrote in message
...
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

"Peter T" <peter_t@discussions wrote in message
...
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

"Quahaug" wrote in message
ps.com...
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,
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").Se lect
Selection.ShapeRange.PictureFormat.TransparentBack ground = msoTrue
Selection.ShapeRange.PictureFormat.TransparencyCol or = RGB(255, 255,
255)
Selection.ShapeRange.Fill.Visible = msoFalse

Manually same results.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
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


"NickHK" wrote in message
...
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

"Peter T" <peter_t@discussions wrote in message
...
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

"Quahaug" wrote in message
ps.com...
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

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

"NickHK" wrote in message
...
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").Se lect
Selection.ShapeRange.PictureFormat.TransparentBack ground = msoTrue
Selection.ShapeRange.PictureFormat.TransparencyCol or = RGB(255, 255,
255)
Selection.ShapeRange.Fill.Visible = msoFalse

Manually same results.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
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


"NickHK" wrote in message
...
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

"Peter T" <peter_t@discussions wrote in message
...
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

"Quahaug" wrote in message
ps.com...
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,
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

"Peter T" <peter_t@discussions wrote in message
...
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

"NickHK" wrote in message
...
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").Se lect
Selection.ShapeRange.PictureFormat.TransparentBack ground = msoTrue
Selection.ShapeRange.PictureFormat.TransparencyCol or = RGB(255, 255,
255)
Selection.ShapeRange.Fill.Visible = msoFalse

Manually same results.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
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


"NickHK" wrote in message
...
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

"Peter T" <peter_t@discussions wrote in message
...
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

"Quahaug" wrote in message
ps.com...
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

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


"NickHK" wrote in message
...
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

"Peter T" <peter_t@discussions wrote in message
...
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

"NickHK" wrote in message
...
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").Se lect
Selection.ShapeRange.PictureFormat.TransparentBack ground = msoTrue
Selection.ShapeRange.PictureFormat.TransparencyCol or = RGB(255,
255,
255)
Selection.ShapeRange.Fill.Visible = msoFalse

Manually same results.

NickHK

"Peter T" <peter_t@discussions wrote in message
...
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


"NickHK" wrote in message
...
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

"Peter T" <peter_t@discussions wrote in message
...
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

"Quahaug" wrote in message
ps.com...
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