Finding centre of activesheet

[Stretchwickster]

I am trying to display an image on the first sheet excel loads (as a splash screen before the macro begins execution). It would be useful to know where the centre of the screen is so that on different systems (with different resolution settings) the image will be central to the sheet. Can anyone help? Clive

 

[RobBroekhuis]

I think you're looking for the activewindow.width and .height properties (but if not, please explain further).
Rob

 

[BillPower]

Why not put your image on a User Form - UserForm1. If you have an existing auto open macro, just paste the blue text into the start of it.

Sub Autpen()
Dim Start, ShowFor
Load UserForm1
UserForm1.Show
ShowFor = 3 'display form for 3 seconds
Start = Timer
Do While Timer < Start + ShowFor
DoEvents
Loop
Unload UserForm1
End Sub

Set UserForm1's ShowModal property to False.
Set UserForm1's Caption to &quot;My App Name&quot;
Set UserForm1's BorderStyle to fmBorderStyleNone
This works ok for Excel 2000+, not sure about '97.

 

[Stretchwickster]

Thanks for your suggestions guys - they are both very helpful answers. The only problem is that there's a time lag before my code executes (see thread707-444782). It's during this time lag that I would like to show the splash screen! Catch 22! Clive

 

[BillPower]

Hi Clive,

If you like, e-mail me your workbook, remove any sensitive data first and I'll have a look at it tonight for you. Will post any suggestions here within 24 hours. billpower@cwcom.net

 

[Stretchwickster]

Thanks for the offer billpower, but I'm not authorised to send this workbook. Thanks anyone for your help. Did you read my other thread? If so, do you have any suggestions off the top of your head? Clive

 

[Stretchwickster]

> Thanks anyone for you help
should have read &quot;Thanks anyway for your help&quot;! Clive

 

[BillPower]

If your workbook does a lot of calculations, try setting Calculation to manual, see if that has any effect.

In your Autpen sub, immediately after Sub Autpen() type in, in a new line, Msgbox &quot;Stretchwickster&quot;, time how long that takes to popup after opening the workbook. Sorry I don't know how experienced you are.

 

[Stretchwickster]

Following your suggestion, I tried setting Calculation to Manual. This had no effect on the time lag but produced an error that doesn't occur when Calculation is set to Automatic! I added the Msgbox line and used this in the above tests. I'm not that experienced as I have only looked at VBA in the last few months. Clive

 

[euskadi]

Rather than put an image on the center of the screen, why not create one sheet as your splash screen, name the range you want to use as your splash with the image in the center and zoom the window when you open up the workbook...

something like:

Application.ScreenUpdating = False
Sheets(&quot;splash&quot.Select
Application.Goto Reference:=&quot;splash&quot;
ActiveWindow.Zoom = True
Application.ScreenUpdating = True