center shape and/or picture on active screen

[maruapo]

Hi

I have a picture or, more accurately, a SHAPE that VBA copies off one sheet and pastes in another. It works just fine except I want the shape pasted in the center of the active screen so the user is greeted with this picture upon activation of the macro. Since the cursor is rarely if ever even close to the center of the screen, I'm not sure how to instruct VBA where exactly to paste the pic.

Here's my code. Any suggestions how to force VBA to paste this picture in the center of the active screen?

--Thankx
maruapo

w = ActiveWorkbook.Name
s = ActiveSheet.Name

Workbooks("Macros.xls").Activate
Sheets("Waiting Room").Visible = True
Sheets("Waiting Room").Activate
ActiveSheet.Shapes("OneMoment").Copy
Sheets("Waiting Room").Visible = False
Workbooks(w).Activate
Sheets(s).Activate
Application.ScreenUpdating = True
ActiveSheet.PasteSpecial Format:="MS Office Drawing Object", Link:=False, DisplayAsIcon:=False
Application.ScreenUpdating = False
ActiveSheet.Shapes("OneMoment").Delete

 

[Legacy 98055]

This will provide an approximate center for a shape named Rectangle 1 in the activewindow... See the visiblerange property in VBA help for the reason why the position may not be exact. Partial ranges that are visible on the bottom and right are inclusively returned as part of the visible range...

<table border="1" bgcolor="White"><caption ALIGN=left><font size="2" face=Courier New>Example VBA Code:</FONT></caption><tr><td><font size="2" face=Courier New>  <font color="#0000A0">Sub</font> Example()
      CenterShape Me.Shapes("Rectangle 1")
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
  
  <font color="#0000A0">Public</font> <font color="#0000A0">Sub</font> CenterShape(o <font color="#0000A0">As</font> Shape)
       o.Left = ActiveWindow.VisibleRange(1).Left + (ActiveWindow.VisibleRange.Width / 2 - o.Width / 2)
       o.Top = ActiveWindow.VisibleRange(1).Top + (ActiveWindow.VisibleRange.Height / 2 - o.Height / 2)
  <font color="#0000A0">End</font> <font color="#0000A0">Sub</font>
</FONT></td></tr></table>