Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

  1. #1
    Join Date
    27th September 2004
    Posts
    26

    Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Hello,

    I'm struggling with adapting this code:

    http://www.cpearson.com/excel/formposition.htm

    for using in Excel 2007/ 2010. The Problem with the old code is: There are metrics which are based on the old (pre-2k7) commandbars & menus, not on the new ribbon. The main question seems to me: How to measure the height of the Ribbon Pane? At the moment I have nothing but a vague idea. Maybe it' possible to find out the measure of the ribbon pane with api-functions. But I'm stuck from the start. Below I'm trying to find the handle of the ribbon pane. I get an hwnd, but this is the wrong one, it's the bar on the bottom of the screen. I'm using this tool for validating my attempts: http://www.xcelfiles.com/API_06.html.

    The first question: What classnames in which order to choose, to get the ribbon handle?

    If this is solved and my idea is still the route to go, how could it ported to the pearson-code? Thank you for help.



    Code:
    Declare Function GetWindowRect Lib "User32" (ByVal hWnd As Long, Rect As Rect) As Long
    Declare Function FindWindowEx Lib "user32.dll" Alias "FindWindowExA" (ByVal hwndParent As Long, ByVal hwndChildAfter As Long, ByVal lpszClass As String, ByVal lpszWindow As String) As Long
    Declare Function FindWindowA Lib "User32" _
       (ByVal lpClassName As Any, _
       ByVal lpWindowName As String) As Long
    
    Private Type Rect
      Left As Long
      Top As Long
      Right As Long
      Bottom As Long
    End Type
    
    
    Sub FindRibbonHandleNSize()
    
    Dim RibbonHwnd As Long
    Dim ExcelMainHwnd As Long
    Dim aRect As Rect  ' receives the rectangle of the window
    
    
    ExcelMain = FindWindowA("XLMAIN", Application.Caption)
    RibbonHwnd = FindWindowEx(ExcelMain, 0, "Excel2", vbNullString)
    RibbonHwnd = FindWindowEx(RibbonHwnd, 0, "MsoCommandBar", vbNullString)
    RibbonHwnd = FindWindowEx(RibbonHwnd, 0, "MsoWorkPane", vbNullString)
    RibbonHwnd = FindWindowEx(RibbonHwnd, 0, "NUIPane", vbNullString)
    RibbonHwnd = FindWindowEx(RibbonHwnd, 0, "NetUIHWND", vbNullString)
    
    
        Call GetWindowRect(RibbonHwnd, aRect)
          Debug.Print " Left: " & aRect.Left
          Debug.Print " Right: " & aRect.Right
          Debug.Print " Top: " & aRect.Top
          Debug.Print " Bottom: " & aRect.Bottom
          Debug.Print " Width: " & (aRect.Right - aRect.Left)
          Debug.Print " Height: " & (aRect.Bottom - aRect.Top)
    
    End Sub
    Last edited by Chris Drontheim; February 23rd, 2012 at 19:21. Reason: Hello Admin, could you this post move to the For free help section, thank you

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    2,136

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Code:
    application.CommandBars("Ribbon").Height
    will give you the height of the ribbon.
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

  3. #3
    Join Date
    27th September 2004
    Posts
    26

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Hello Rory, thank you. That simple, huh.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    2,136

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Yes, but that doesn't include the formula bar, which of course can be resized...

    Most annoying is that the Excel chart window no longer exists - that was by far the simplest way to position a userform, in my opinion.
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

  5. #5
    Join Date
    27th September 2004
    Posts
    26

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Thank for the hint. Could the first question modified to: How to find out, what size the formular bar is?

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    2nd May 2008
    Location
    Ubique
    Posts
    2,136

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Simplest thing is probably to locate the part containing the Name box and use that:
    Code:
    Sub FindRibbonHandleNSize()
         
        Dim RibbonHwnd As Long
        Dim ExcelMain As Long
        Dim aRect As Rect ' receives the rectangle of the window
         
         
        ExcelMain = FindWindowA("XLMAIN", Application.Caption)
        RibbonHwnd = FindWindowEx(ExcelMain, 0, "Excel;", vbNullString)
         
         
        Call GetWindowRect(RibbonHwnd, aRect)
        Debug.Print " Left: " & aRect.Left
        Debug.Print " Right: " & aRect.Right
        Debug.Print " Top: " & aRect.Top
        Debug.Print " Bottom: " & aRect.Bottom
        Debug.Print " Width: " & (aRect.Right - aRect.Left)
        Debug.Print " Height: " & (aRect.Bottom - aRect.Top)
         
    End Sub
    Rory
    Theory is when you know something, but it doesnít work. Practice is when something works, but you donít know why. Programmers combine theory and practice: nothing works and they donít know why

  7. #7
    Join Date
    27th September 2004
    Posts
    26

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Yes, good one. Maybe this is even better:

    ExcelMain = FindWindowA("XLMAIN", Application.Caption)
    formulaBarHwnd = FindWindowEx(ExcelMain, 0, "EXCEL<", vbNullString).

    Is seems, "EXCEL<" is the classname of the formula-window.

    Thank you very much.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    5th August 2011
    Posts
    8

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Code:
    Sub GetScreenStats()
        ZFS = ActiveWindow.Zoom / 100
        Hdc = GetDC(0)  ' find pixels to Points ratio  (3:4)
        PixToPts.x = 72 / (GetDeviceCaps(Hdc, 88))
        PixToPts.y = 72 / (GetDeviceCaps(Hdc, 90))
        ReleaseDC 0, Hdc
        PtsToPix.x = 1 / PixToPts.x  ' both 4/3
        PtsToPix.y = 1 / PixToPts.y '   
        ' the offset in pts  from edge of screen to top left cell
        OffsetPT.x = ActiveWindow.PointsToScreenPixelsX(0) * PixToPts.x
        OffsetPT.y = ActiveWindow.PointsToScreenPixelsY(0) * PixToPts.y
        Set ActWinVis = ActiveWindow.Application.ActiveWindow.VisibleRange
         AppFormOffPt.x = ActiveWindow.Left * 2 + 10 ' why doubled  10 is 2 borders
         AppFormOffPt.y = ActiveWindow.Top
         
         
    'ActiveWindow.Application.ActiveWindow.VisibleRange
    End Sub
    The main useful trick seems to be

    OffsetPT.x = ActiveWindow.PointsToScreenPixelsX(0) * 3/4 ' PixToPts.x
    OffsetPT.y = ActiveWindow.PointsToScreenPixelsY(0) *3/4 ' PixToPts.y
    this gives the location on the screen of the left top of cell A1..
    no mater what height of menu on the top or what side width menu bars you have on the left ''
    or what cell you have scrolled to ... if you have cell AM1456 left top then A! is well off the screen
    Code:
    'since people like to change screen size max min
    ' we need functions that will recalculate each time
    'in the event that windows are zoomed , scrolled
    'or if menus top or left are resized inserted or deleted
    'you need to run a tidy routine to  correctly reposition the objects
    '
    'hopefully you can sort out some ideas from the   code below
    '
    
    Declare Function GetDC& Lib "user32" (ByVal hwnd&)
    Declare Function GetForegroundWindow& Lib "user32" ()
    Declare Function ReleaseDC& Lib "user32" (ByVal hwnd&, ByVal Hdc&)
    Declare Function GetDeviceCaps& Lib "gdi32" (ByVal Hdc&, ByVal nIndex&)
    Declare Function GetCursorPos& Lib "user32" (lpPoint As LPoint)
    Declare Function SetCursorPos& Lib "user32" (ByVal x&, ByVal y&)
    Public Enum MovNum
        generalpt = 1
        FormPt = 2
        MouseToPt = 3
        MouseMovePt = 4
    End Enum
    Public Type LPoint
        x As Long
        y As Long
    End Type
    Public Type SPoint
        x As Single
        y As Single
    End Type
    Type MouseCommand      ' INPUT structure
        iType As Long        ' 0 for mouse, 1 for kbd
        iDx As Long          ' rel movt in pixels (unless ABSOLUTE)
        iDy As Long
        iWheelData As Long   ' we don't use this
        iFlags As Long       ' we use this (see MOUSEEVENT flags below)
        iTime As Long        ' don't use this
        iXtra As Long        ' or this
    End Type
    Const SM_CYCAPTION As Long = 4
    Const MOUSEEVENTF_LEFTDOWN = &H2              ' left button down
    Const MOUSEEVENTF_LEFTUP = &H4                ' left button up
    Const MOUSEEVENTF_MOVE As Long = &H1          ' mouse move
    Const MOUSEEVENTF_RIGHTDOWN As Long = &H8     ' right button down
    Const MOUSEEVENTF_RIGHTUP As Long = &H10      ' right button up
    Const MOUSEEVENTF_MIDDLEDOWN As Long = &H20   ' middle button down
    Const MOUSEEVENTF_MIDDLEUP As Long = &H40     ' middle button up
    Const MOUSEEVENTF_ABSOLUTE As Long = &H8000   ' absolute move
    Const MOUSEEVENTF_WHEEL As Long = &H800       ' wheel button rolled
    Declare Function SendInput& Lib "user32.dll" _
                                (ByVal nCommands&, iCommand As MouseCommand, ByVal cSize&)
    Public PixToPts As SPoint, PtsToPix As SPoint
    Public ZFS!, Hdc&, OffsetPT As LPoint, AppFormOffPt As LPoint, CtrlOb   ' As Control
    Public IsMoveOb As Boolean, ActWinVis As Object
    
    Type Wob  ' working Object
        PassName As String  ' passed string to get data from
        ObType As String
        Name As String
        SubName As String
        MoveType As String
        MoveNum As Integer
        Ob As Object
        ZoomForm As Single  ' for finding controls in a zoomed form
        WXPer As Single    ' width or x values percent to pick point
        HyPer As Single    ' height or y  values percent to pick point
        OFFWX As Single    ' offset width or X   from picked point
        OFFHY As Single  ' offset height or Y from picked pt
        ' the offsets can be uses to hit on buttons on right of forms etc
        'not much used
        TLtoPickPt As LPoint  ' the offset from top left to pick point
        PtPix As LPoint  ' a general Pt in Pix used for cursor
        PtPts As LPoint    ' a general Pt in Points
    End Type
    ' Move Object      To Object  and Blank Object
    Public Mob As Wob, Tob As Wob, Bob As Wob
    Sub MouseAndClick()
        Dim mCommand As MouseCommand
        With mCommand
            .iFlags = MOUSEEVENTF_LEFTDOWN
            SendInput 1&, mCommand, Len(mCommand)
            .iFlags = MOUSEEVENTF_LEFTUP
            SendInput 1&, mCommand, Len(mCommand)
        End With
    End Sub
    '                               A    B    C   D   E   F
    '  Screen to sheet Margin  !    work sheet area columns     *Object        right of worksheet areas
    '<-----  Offset --------- 1 >    |   |   |   |   |   |   |   |   |   |   |   |   |   |
    '            side menus   2
    '
    '.......... pixels from side of screen IS Mouse CURSOR......>*  (Pixel = .75 of point most times)
    '
    '                    Pixels refer to screen dots and are not changed by zooming
    '.......... Form points (FPt).....(No zooming)...............>*   (but a form zooms inside it )
    '
    '           ( so a mouse position is always  Form * 4/3  .) .both not zoomed
    '
    '  .... UnZoomed offset... !....sheet  can be zoomed .General  Points  (GPt) ...>*
    ' ..  So Below is how to change
    ' The main  clue  is that the
    '   ActiveWindow.PointsToScreenPixelsX(0)
    '   gives the side of screen offset in pixels ..
    '   for all scrolls margins and side menus   ( I hope )
     'These are coded as
     'GPt as general Point  .. shapes including ole objects
     '   Mo as mouse cursor,
    ' Fo as form .. normal .. child of application has lots of funny app tops and lefts
    Function GPtMo(Gpt As LPoint) As LPoint
        GPtMo.x = (Gpt.x * ZFS + OffsetPT.x) * PtsToPix.x
        GPtMo.y = (Gpt.y * ZFS + OffsetPT.y) * PtsToPix.y
    End Function
    Function MoGpt(Mpt As LPoint) As LPoint
        MoGpt.x = (Mpt.x * PixToPts.x - OffsetPT.x) / ZFS
        MoGpt.y = (Mpt.y * PixToPts.y - OffsetPT.y) / ZFS
    End Function
    Function MoFo(Mpt As LPoint) As LPoint
        MoFo.x = Mpt.x * PixToPts.x
        MoFo.y = Mpt.y * PixToPts.y
    End Function
    Function FOMo(Fpt As LPoint) As LPoint
        FOMo.x = Fpt.x * PtsToPix.x
        FOMo.y = Fpt.y * PtsToPix.y
    End Function
    Function GPtFo(Gpt As LPoint) As LPoint
        GPtFo.x = Gpt.x * ZFS + OffsetPT.x
        GPtFo.y = Gpt.y * ZFS + OffsetPT.y
    End Function
    Function FoGPT(Fpt As LPoint) As LPoint    ' inv of above
        FoGPT.x = (Fpt.x - OffsetPT.x) / ZFS
        FoGPT.y = (Fpt.y - OffsetPT.y) / ZFS
    End Function
    Sub DoMoveMT()
        If Mob.ObType <> "Cursor" Then
            ' changeing width, height of move ob  to sizes of to object  .. not < 10%
            Mob.Ob.Left = Tob.PtPts.x - Mob.TLtoPickPt.x
            Mob.Ob.Top = Tob.PtPts.y - Mob.TLtoPickPt.y
        Else
            ' MsgBox Tob.PtPts.X
            SetCursorPos Tob.PtPts.x, Tob.PtPts.y
        End If
    End Sub

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    27th September 2004
    Posts
    26

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Hi Harrys,

    that looks very promising, I'll try to figure it out. Thank you very much. For my inital problem I've taken another route. At http://proexceldev.net I've found an updated Version of their Form-positioning-module, which is very good. Sadly no hyperlink possible, you have to register over there. But worth a look, if you are still interested in.

    Viele GrŁŖe

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    3rd September 2010
    Posts
    1

    Re: Form Positioning in Excel 2007/2010 / Api & Windows-Classnames

    Hello Harrys,

    Does your procedure take into account that as the row number increases the left hand edges of _all_ the cells moves to the right because the width of all row labels is adjusted to accommodate the largest value.

    For example using the Mio Screen Ruler I positioned the _zero value_ of the ruler at the left hand edge of the screen and the _left hand border_ of Cell A1 at 100 Pixels as measured by the ruler

    Then
    Display Row 1 Hide Rows 2 to 99
    Display Row 100 Hide Rows 101 t0 999
    Display 1000 Hide Rows 1001 to 9999

    The width of the row label increased by approximately 13 Pixels _but_ the left hand edges of all of the displayed cells remains vertically aligned.

    Would welcome your advice on how to overcome this effect

    many thanks


    Mordred

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 3
    Last Post: February 23rd, 2012, 19:43
  2. Macro to open the file->options form in excel 2010
    By iamquitting in forum EXCEL HELP
    Replies: 2
    Last Post: January 30th, 2012, 18:37
  3. Replies: 1
    Last Post: May 8th, 2011, 04:55
  4. How to add Datepicker in Excel 2010 VBA Form?
    By rksample in forum EXCEL HELP
    Replies: 1
    Last Post: February 25th, 2011, 03:30
  5. .XLS Calculations Crash Excel 2007 in Windows XP Pro and not in Windows 7 - Why?
    By Dave Hawley in forum Office 2007 Migration and Application Compatibility
    Replies: 0
    Last Post: March 26th, 2010, 02:07

Bookmarks

Posting Permissions

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