I need an Excel macro

  Chris the Ancient 16:39 25 Oct 2003

Hopefully that catches VoG's attention!

I need a macro that will work in all versions of Excel from 95 up... If it can be done...

I am designing a suite of spreadsheets that have a corporate logo as a splash page as the first worksheet of each book. No hassles in using Auto_Open() to do that!

The bit I would like to achieve is that the logo image is centred on the worksheet - regardless of the screen resolution.

Can it be done?


  VoG II 17:05 25 Oct 2003

I think that you will need to determine the screen resolution then do some sums based on the size of the image.

The following code will return the monitor's settings:

'place these statements at the top of your code
Declare Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long

Const SM_CYSCREEN As Long = 1

Const SM_CXSCREEN As Long = 0

Sub GetScreenDimensions()

Dim lWidth As Long

Dim lHeight As Long

lWidth = GetSystemMetrics(SM_CXSCREEN)

lHeight = GetSystemMetrics(SM_CYSCREEN)

MsgBox "Screen Width = " & lWidth & " " & "Screen Height = " & lHeight

End Sub

I'm not sure how you are planning to display the logo. If on a userform then this illustrates how to make a userform fill the screen:

Private Sub UserForm_Activate()

With Application

Me.Top = .Top

Me.Left = .Left

Me.Height = .Height

Me.Width = .Width

End With

End Sub

The above code goes in the userform's code module.

I hope that the above is the sort of thing you are after!

  Chris the Ancient 17:13 25 Oct 2003

Far too slow!! You can go quicker than that.

Seriously though, a lot of food for thought for me to try there! It will take a little while (as I'm also in the middle of doing tons of printing for my work).

I'm fascinated by the thought of doing it using a screen-filling form. I hadn't thought of that!

I shall try out both ideas and feedback. (But don't expect instant feedback, I'm in the forum with one of my spare pairs of hands!) Probably get back later this evening.

But being well aware of the wisdom of the sage VoG, I have enough confidence to click the 'resolved' box!



  Chris the Ancient 12:17 26 Oct 2003

While the site has been having a well-earned rest from all the hard work last night, VoG (bless his heart) did a lot of work for me and gave me a solution.

While Excel XP has modal capability on forms, earlier versions from 97 and before don't, so once a form opened, it is impossible to execute code until the form is closed again. However, VoG found a solution (but don't he always?)

One needs to generate a form and use the code properties for that form of UserForm_Activate()

By inputting the following code...

Private Sub UserForm_Activate()

Dim PauseTime, Start

PauseTime = 3 ' Set duration.
Start = Timer ' Set start time.
Do While Timer < Start + PauseTime
DoEvents ' null events - just to keep screen active

Unload Me

End Sub

I have a userform that opens as a splash screen and then disappears after 3 seconds.

So, the remarkable VoG (new idea for a handle?) strikes again and has produced an extremely happy bunny (again).

Many thanks VoG


  VoG II 12:19 26 Oct 2003

It gave me something to do while the site was down!

  Chris the Ancient 12:22 26 Oct 2003

...talking about how I spent the evening instead of being here (large glasses of wine etc.), I'm sure FE would quickly lock the thread.

So I won't ;o)


  VoG II 12:30 26 Oct 2003

I've just had a look at the workbook that you sent me and very neat it is too!

A lot more compact than the screeds of drivel that I sent you! I blame it on the wine, G&Ts and so on that I consumed last night.

  Chris the Ancient 12:39 26 Oct 2003

...I followed.

My old headmaster at school said to me one day, as I was scratching away with my slate and pencil and referring to my old log tables) that... "all good mathemeticians are lazy."

On that basis, I must be a flippin' brilliant mathemetician!

My wife will always tell you that I opt for the easy, shortest and laziest option when I can (usually to comments of... "yes, dear!")


  A_World_Maker 13:21 26 Oct 2003

wouldn't font default size effect the layout? Assuming you dont have control over which default font they use on their system (combination of Screen resolution and system font size?).

  Chris the Ancient 14:05 26 Oct 2003

The beauty of using the UserForm system is that once you have used that form to produce your 'spash screen' the form stays as a defined size. It doesn't matter what the user's default font size is because you define your own font characteristics for the form when you design that form.

Then, when you call up that UserForm, provided that you have set all the appropriate properties thus, the UserForm opens at its preset size in the middle of the screen - regardless of screen size/resolution.



This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Fujitsu Lifebook P727 laptop review

Best of the Grad Shows 2017: University of the West of England (UWE)

Best value Mac: Which is the best £1249 Mac to buy

Les meilleures GoPro 2017