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