More on disappearing shapes

  • Thread starter Thread starter Cheer
  • Start date Start date
C

Cheer

Earlier I noted that a shape seems to disappear from Excel 2003, but
not Excel 2000 when it's added using AddPicture. But it doesn't always
happen, so I've done some more testing.

If I use this method, the shape always appears:

Option Explicit

Sub testit()
Dim earl As String
earl = "http://www.footyprogs.fsnet.co.uk/everton.gif"
Get_Image Sheet1, earl
End Sub

Public Sub Get_Image(wsSheet As Worksheet, strURL As String)
Dim shape As shape, shChart as shape

Set shChart = wsSheet.Shapes.AddPicture(strURL, False, True, 200,
100, 600, 400)

For Each shape In wsSheet.Shapes
shape.Placement = xlFreeFloating
shape.Visible = msoTrue
Next

shChart.ScaleWidth 1#, msoTrue, msoScaleFromTopLeft
shChart.ScaleHeight 1#, msoTrue, msoScaleFromTopLeft

Set shChart = Nothing
End Sub

BUT... If I define a UserForm to do this picture fetching, the shape
only "appears" (I'll explain what I mean by that in a moment), if I
run the above "testit" routine first. If I click on the userform
button first, the shape is there, but in the background of Sheet1. The
userform routines are listed below, and I simply "run" the userform
and then click on the "OK" button (CommandButton1):

Option Explicit
Option Base 1

Dim oHTTP As MSXML2.XMLHTTP30

Private Sub CommandButton1_Click()
Dim earl As String
earl = "http://www.footyprogs.fsnet.co.uk/everton.gif"
Get_Image ActiveWorkbook.Worksheets("Sheet1"), earl

Unload Me
End Sub

Private Sub userform_initialize()
Set oHTTP = New MSXML2.XMLHTTP30
End Sub

Private Sub UserForm_Terminate()
Set oHTTP = Nothing
End Sub

Private Sub CommandButton2_Click()
Unload Me
End Sub

Note that Microsoft XML, v. 3.0 is selected as a reference to the
project, even though the oHTTP object is never used for anything.

When I say the object "appears," I mean it is in the foreground and
visible - in Excel 2003 when it is placed on the sheet, it appears to
be in the background - I can select it (and see the selection
handles), but it's not visible when I go to that sheet.

Two other items of interest: If I take the oHTTP object statements
out, the image appears as it should. (But I need those statements in
for another part of the project).

If I move the object in Excel 2003, it suddenly jumps to the
foreground, but if I try to move it via VBA, it remains in the
background.

Has anyone else experienced this or might anyone have a solution to
this problem?
 
Cheer,

I'm not following the complexities of this, but I've had success in the past
with Repaint to get pictures to show. Have you tried that?

hth,

Doug
 
Cheer,

I'm not following the complexities of this, but I've had success in the past
with Repaint to get pictures to show. Have you tried that?

hth,

Doug
Click to expand...

Doug,

It is a weird, complex issue, and I'm sure I've not done a stellar job
of explaining it.

Sadly, the Repaint method does not apply to shapes, only pages (of a
multiform) and userforms. Neither does Refresh or Reset.

But, you reminded me that the "Picture" Toolbar has a "Reset Picture"
command on it, and when I use that I render my shape visible. Might
you (or anyone) know how to invoke that using VBA?

Thanks for taking a crack at this.

Regards,
Cheer
 
vba can move without it becoming active, the mouse makes it active
Click to expand...


Thanks for your reply, Jim. I guess I need to ask then, how do I make
it active with VBA? My shape is on the sheet, but it does appear to be
in some sort of "inactive" state - so how do I make it visible or
active with code rather than making my users select it with the mouse
and choose "Reset Picture"?
 
Back
Top