More on disappearing shapes

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

On Thu, 21 Apr 2005 14:58:31 -0700, "Doug Glancy"
<[email protected]> wrote:

>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" <[email protected]> wrote in message
>news:[email protected]...
>> 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?
>>
>

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

"Cheer" wrote:

> On Thu, 21 Apr 2005 14:58:31 -0700, "Doug Glancy"
> <[email protected]> wrote:
>
> >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" <[email protected]> wrote in message
> >news:[email protected]...
> >> 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?
> >>
> >
>
> 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
>
>

On Thu, 21 Apr 2005 21:16:02 -0700, Jim at Eagle
<[email protected]> wrote:

>vba can move without it becoming active, the mouse makes it active
>
>"Cheer" wrote:
>
>> On Thu, 21 Apr 2005 14:58:31 -0700, "Doug Glancy"
>> <[email protected]> wrote:
>>
>> >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" <[email protected]> wrote in message
>> >news:[email protected]...
>> >> 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?
>> >>
>> >
>>
>> 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


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"?