Solved: Very helpful piece of code

[Amanda1]

No, I don't have one - I'd like one.

I'm getting very tired of having to continually make windows smaller and larger when I'm looking at multiple applications. :mad: Unfortunately the Windows/New/Arrange function makes windows a regulated size, which doesn't help when I would like one window to be smaller, another slightly larger etc etc. As for dragging the sides to adjust the size - that is tedious.

What I would love, is for there to be a small button somewhere on each application main menu bar that has a macro attached to it - when you press and hold the button, the window will "shrink" proportionally. When you release the button the window remains at that size. You can then double click the button for it to revert to standard size - or if it was really clever, you could double click/hold and the window would "grow" proportionally - again, release the button and the window stops "growing".

At the risk of giving my age away "Wouldn't that be luverly?"

Now, I can do the hard bit of selecting the button and getting it on the menu bar - the easy bit of the supporting macro, I thought some of you might like to have a go at. Yes/No? :thinking:

Cheers

Amanda :winking2:

[Ken Puls]

HI Amanda,

Give this a shot:

Dim aw As Window
Set aw = ActiveWindow
With aw
'Set width and height
.Width = 800
.Height = 200
'Position top left corner of window
.Left = 1
.Top = 1
End With

HTH,

[TonyJollans]

Hi Amanda,

All I want is a room somewhere,
Far away from the cold night air ...


Guess I'm getting old too. I love the idea you've got but what I'd like is for windows to behave more like panes in some circumstances, so that as one shrunk the other(s) grew - not sure about the proportional bit though.

I don't see any real reason why a macro couldn't do as you suggest in an Office application (others might be more difficult) - I might have a play with it.

[Ken Puls]

Doh! :doh:

Somehow I missed the whole "multiple applications" part of this question... it must have been the Excel forum or something...

Sorry, Amanda, I was just thinking of resizing the excel windows using code.

[Amanda1]

Hi Ken

Don't worry - I've got a perfect use for the script you have just written - the staff are on Excel 2000 that doesn't size the "window" function and I'm working on a book where they need two sheets open simultaneously - so this script is perfect & I've just added it in.

Now, if yourself and Tony are feeling bored & would like to work on the 1.B version, that will be great.

Take care & thanks again:sparkle:

Amanda. PS I've lived in various parts of Norfolk, say hello to the Broads for me. (Just to clarify for any American friends, the Norfolk Broads are not members of the female gender, they are waterways).

[Ken Puls]

Amanda,

Quick question for you... which apps would be open and included in this?

I can see it being... well... not easy, but certainly doable for any office apps. The non-office apps would take someone with more programming knowledge than I have right now though.

But if you wanted to do say... Word on the left, and split Excel and Powerpoint on the right half of the screen, I think something could be done there. Never tried it, but could be fun to go there. :)

[Amanda1]

Hi Ken

That's basically what I'm looking for - the majority of the time it is Office apps that are most often open - swapping from Outlook where you have opened a Word attachment, then maybe transferring info across to Excel and perhaps copying it to another sheet etc. In fairness, this is probably the most frequent scenario. But just to be able to put a button on each header that will diminish and resize while the button is being held would cut out a huge amount of time for office workers who keep swapping from one to the other regularly.

Cheers

Amanda:yes

[Ken Puls]

Hi Amanda,

I know this isn't exactly what you're after, but it might give you a start on playing. I dug up some interesting things about our friendly office apps in this... mainly in their inconsistencies in dealing with items!

The code below will bind to the active instance of Word, Excel & Outlook. It scales Word to take up the left hand side of the screen, Excel to take up the upper right quarter, and Outlook to take the lower right corner. No, at this point it doesn't have any increase/decrease feature to it. I also haven't tested it much with multiple instances of the programs open, so I'm not sure how it will react to that.

It does use a late binding approach, and while I've only tested it from Excel, it should work from any of the office apps involved here.

The thing that is so weird is that every app has a different constant needed for the WindowState argument, and Outlook, unlike it's cousins, needs screen sizes set in pixels, not points. Just bizarre.

At any rate, here's the code to date. It has an API, so this should all go at the top of the module:

Option Explicit

Private Declare Function GetSystemMetrics Lib "user32.dll" _
(ByVal nIndex As Long) As Long
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1
Const PixToPoint = 0.75

Sub ResizeScreens()
'Macro created 11/24/2005 22:41 by Ken Puls
'Macro Purpose: To make Word use left half of screen, Excel use
' upper right quarter, and outlook lower right quarter of screen
'NOTE: Word, Excel, Powerpoint all use Points (~.75 pixels) to set
' screen sizes. Outlook uses pixels, not points

Dim x As Long, y As Long, lTskBr As Long
Dim xlApp As Object, wdApp As Object, olApp As Object
Dim sMissing As String

'Set height of start menu bar in pixels
lTskBr = 20

'Bind to each of the required applications
On Error Resume Next
Set wdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
sMissing = "Word or "
Err.Clear
End If
Set xlApp = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
sMissing = sMissing & "Excel or "
Err.Clear
End If
Set olApp = GetObject(, "Outlook.Application")
If Err.Number <> 0 Then
sMissing = sMissing & "Outlook or "
Err.Clear
End If
If Len(sMissing) > 0 Then
MsgBox "Sorry, but I could not find valid instance(s) of " & vbNewLine & _
Left(sMissing, Len(sMissing) - 4), vbOKOnly + vbCritical, "Missing App!"
Exit Sub
End If
On Error GoTo 0

'Get Screen Metrics in Pixels
x = GetSystemMetrics(SM_CXSCREEN)
y = GetSystemMetrics(SM_CYSCREEN)

'Set screen size & position for Word Application
'Set to left half of screen
'Word measurements must be in points
With wdApp
.WindowState = 0
.Top = 0
.Left = 0
.Width = 0.5 * x * PixToPoint
.Height = (y * PixToPoint) - lTskBr
End With

'Set screen size & position for Excel Application
'Set to top right quarter of screen
'Excel measurements must be in points
With xlApp
.WindowState = 1
.Top = 0
.Left = (0.5 * x * PixToPoint) + 1
.Width = 0.5 * x * PixToPoint
.Height = (0.5 * y * PixToPoint) - (lTskBr / 2 * PixToPoint)
End With

'Set screen size & position for Outlook Application
'Set to bottom right quarter of screen
'Outlook measurements must be in pixels
With olApp.ActiveExplorer
.WindowState = 2
.Top = (0.5 * y) - (lTskBr / 2 / PixToPoint)
.Left = (0.5 * x)
.Width = 0.5 * x
.Height = (0.5 * y) - (lTskBr / 2 / PixToPoint + 1)
End With

End Sub

[Cyberdude]

Have you considered making this a KB submittal?

[Ken Puls]

Have you considered making this a KB submittal?

LOL! Actually, I hadn't. I'm still actively working on it and was thinking maybe that it could become an addin of some kind. :)

[Ken Puls]

Just a quick follow up...

I have a working version of this code. I just need to test it with a couple more office apps and package it. I'll hopefully post a Beta version of it tonight.

:)

[Ken Puls]

Hello Amanda1 and anyone else who's interested...

Here is version 1 of "ScreenTiler" for testing.

I've included a few things in the attached zip file:
-ScreenTiler.xla --> a prepackaged excel addin with all the code
-ufScreenTiler.frm & ufScreenTiler.frx --> the userforms which can be imported directly into a VBProject
-modScreenTiler.bas --> the code module which can be imported directly into a VBProject

The xla will work as is in Excel (just install the addin via Tools|Addins.)

If you want to use the functionality in another office app, import the userform and codemodule into the appropriate document/template/code container. As far as the other apps go, I did my best to ensure that it was coded to work from all the other office apps, and I'm fairly sure that it will. The trick will be to get the toolbar to load automatically. I still need to work out (acquire) instructions on how to make that happen easily. I did use AutoOpen procedures, so hopefully it makes it really easy to just import, but I haven't tested that in full as of yet. Tested it from Word, and the code ran without a hitch. :)

The code creates a toolbar which has 8 commands on it:
-Configure Applications is where you choose what three apps you want to use. (Must be three). If you have not run this macro, you will be prompted before you can run any of the others. It only needs to be run once, but is there in case you decide to change programs or want to toggle a division by less than 10% at a time.

-Activate Screen Split is how you activate the screen splitting features that you set in the configuration screen.
-Maximize All Apps will maximize all applications that you toggled

-Decrease & Increase width of left app (2 buttons) will move the left right division.. er.. left or right.
-Decrease & Increase height of upper right app (2 buttons) will... I think you get the picture.

-Version holds the application version.

Not hard to use, but you must have all three apps open, or you will get a hole in your screen and a message that says it couldn't resize one (or more).

Give it a play and let me know if anything works, doesn't work, and/or could be improved.

:)

[Wolfgang]

good morning ken...(08:45 GMT)

i am trying your "very helpful piece of code" and like to ask you why do i "have" to use 3 apps?!

normally i'm using just excel and to some extend word, maybe access...

would your addin not work with just 2 of the above?

have anice day,
wolfgang

[Ken Puls]

Hi Wolfgang,

Well... because I programmed it that way. :)

Seriously, in the original scope, I worked on the 3 app basis. In the setup userform, I forced a use to choose three apps.

I also made another determination in the code that I would not let a user scale any of the apps down to less than 10% of the screen height/width, as I didn't want to do with the issue of having an app "open and invisible" by setting it to no height or width. I figured that would make a real mess of things, especially if it was the one with the toolbar to undo it.

One interesting point, though, is that if you only open two of the three apps you select, it will scale, but only to a max 90% on the right hand side. Now I suppose that I could alter it to allow 100% scaling if only two apps were chosen... I'll have to think on it.

FYI, I forgot to mention that at the current time this does not work with Access or Visio. Only Word, Excel, Powerpoint, Publisher and Outlook.

[Wolfgang]

good morning again ken,

thank you very much for your answer..

I'm glad to learn that you ""programmed it that way" and that it is not a "bug" on my system...

ok, so i selcted excel - word and powerpoint and when i clicked "activate screen split" i got the following error message:

the following application(s) could not be resized:
word, powerpoint

does this tell you anything?!

i'm using xp-pro, us-version and also office 2003, us-version...

best,
wolfgang

[Ken Puls]

Hi Wolfgang,

Did you have all three apps open, or just Excel? It does need the apps open to perform it. When I built it, I elected to capture the current session, rather than create new ones. I suppose I could do both... when activating the split, if a current session is not found, launch one...

Let me know, (and thanks for playing with it ;) )

[Wolfgang]

good morning ken...

thanks for you reply and, no, i only had excel open because of other non-office stuff running...

actually i'm using 90% excel, 50% word and 50% access....

make sense?!

as you can easily see....we are totally organized around here...

take care,
wolfgang

[Rembo]

Hi Wolfgang,


actually i'm using 90% excel, 50% word and 50% access....

I'm the first to admit I'm not a mathematical wizard but somehow this doesn't add up http://vbaexpress.com/forum/images/smilies/001.gif


Rembo

[Wolfgang]

hi rembo...
*** as you can easily see....we are totally organized around here...***

excel - word and access as a single app is equal to 100%...

so, from the total allocated, i use chunks of 90 - 50 and 50% each...

do you believe now that we are totally organized around here?!

because our motto is:
In theory, there is no difference between theory and practice. In practice, there is?

make sense?!

have a great day,
wolfgang

[Rembo]

*** as you can easily see....we are totally organized around here...*** ..
because our motto is:
In theory, there is no difference between theory and practice. In practice, there is?
make sense?!

Right... you been spending a lot of time with the h-c lately haven't you? ;-)

Theory: how it's supposed to work but it doesn't work that way.
Practical: how it's working although that's not how it's supposed to work and nobody knows why.

And that's where theory and practice meet: nothing is working and nobody knows why. http://vbaexpress.com/forum/images/smilies/023.gif

Rembo

[Amanda1]

And I thought it was just me who had a warped sense of humour.:santa:

[Zack Barresse]

Hmm, that would be interesting. An add-in sounds wonderful. Perhaps different configurations one can pick from, much like the AutoFormat feature of Excel? Now THAT would be something of a prize gem indeed! ;)

[Ken Puls]

Hello everyone,

I've attached an updated version here (1.02) for anyone to play with if they like. I'll get to Zack's "predefined configuations" later, but for now, the new version includes the following changes:

-Added support for Access & Visio
-Creates an instance of the application if one is not already present

Now... don't expect it to install Visio for you if you don't have it, though! ;) Future version will check what apps you have installed, and will only let you configure those.

[Ken Puls]

Hey guys,

I found out that someone liked this so much they blogged it! LOL!

http://cat14051.mysinablog.com/index.php?op=ViewArticle&articleId=66003

Hi Emily! :hi:

[Rembo]

Dude, your famous! :clap::yes


I found out that someone liked this so much they blogged it! LOL!

[Emily]

Hey guys,

I found out that someone liked this so much they blogged it! LOL!

http://cat14051.mysinablog.com/index.php?op=ViewArticle&articleId=66003

Hi Emily! :hi:

Hi
Sorry, reproduce the code without your permission. :whip

[Ken Puls]

No worries, Emily. It was a public post. I'm glad you liked it, and I only wish I could read the comments on your site. ;)