Consulting

Page 1 of 2 1 2 LastLast
Results 1 to 20 of 27

Thread: Solved: Very helpful piece of code

  1. #1
    VBAX Regular
    Joined
    Nov 2005
    Posts
    82
    Location

    Solved: Very helpful piece of code

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

    Cheers

    Amanda

  2. #2
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    HI Amanda,

    Give this a shot:

    [vba]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[/vba]

    HTH,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  3. #3
    VBAX Master TonyJollans's Avatar
    Joined
    May 2004
    Location
    Norfolk, England
    Posts
    2,291
    Location
    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.
    Enjoy,
    Tony

    ---------------------------------------------------------------
    Give a man a fish and he'll eat for a day.
    Teach him how to fish and he'll sit in a boat and drink beer all day.

    I'm (slowly) building my own site: www.WordArticles.com

  4. #4
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  5. #5
    VBAX Regular
    Joined
    Nov 2005
    Posts
    82
    Location
    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

    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).
    Last edited by Amanda1; 11-23-2005 at 10:58 AM. Reason: missed something

  6. #6
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  7. #7
    VBAX Regular
    Joined
    Nov 2005
    Posts
    82
    Location
    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

  8. #8
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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:

    [vba]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[/vba]
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  9. #9
    Have you considered making this a KB submittal?

  10. #10
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    Quote Originally Posted by Cyberdude
    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, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  11. #11
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  12. #12
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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.

    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  13. #13
    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

  14. #14
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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.
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  15. #15
    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

  16. #16
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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 )
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  17. #17
    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

  18. #18

    Wink

    Hi Wolfgang,

    Quote Originally Posted by 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


    Rembo

  19. #19
    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

  20. #20
    Quote Originally Posted by Wolfgang
    *** 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.

    Rembo

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •