none
Get Screen Orientation RRS feed

  • Question

  • I’m doing an Excel VBA project that has a user form, and I need a method to detect the screen orientation in real time from the device sensors.  I want the detection of the screen orientation in the Excel and not thought another application.  However searching the net, I haven’t found a method to do it. 
    I managed to do it with reading the screen high & width but for that I needed to create events that detect the screen high & width. 
    Is it possible to read the device orientation sensor using the Excel or are there any library that can be imported in Excel. 
    I use  Win 7 64-bit, Excel 2013.
    I gladly accept any help & information. 
    Thank you.
    Tuesday, March 31, 2015 2:57 PM

All replies

  • VBA (nor Excel) doesn't provide any event for that. Why do you need to handle such events?

    In my opinion a user form should be displayed with the Excel window.

    Tuesday, March 31, 2015 3:24 PM
  • The application will be used on the tablet.
    Depending on the orientation of the tablet the user form appears in two versions.
    Tuesday, March 31, 2015 3:42 PM
  • Are Excel windows appear in two versions?
    Tuesday, March 31, 2015 3:44 PM
  • Yes.

    In addition to classic Landscape EXcel, I have the Portrait Orientation.

    With a subroutine I can  determine the ratio between width (Application.Width) and height(Application.Height) and display the corect user form.

    But I must must associate this subroutine with an event, such pressing a command button.

    I want a function that will start automatically.

    Tuesday, March 31, 2015 4:08 PM
  • Try using the WindowResize event of the Application or Workbook classes.
    Tuesday, March 31, 2015 4:16 PM
  • Thanks.

    I created a class, an Event Handler, but I have some problems with the event handler.

    I have continued working... 


    Tuesday, March 31, 2015 5:09 PM
  • You may find the Getting Started with VBA in Excel 2010 article helpful.
    Tuesday, March 31, 2015 5:10 PM
  • I have problems with my code lines.

    i have my class

    Public WithEvents appevent As Application
           Private Sub appevent_WindowResize(ByVal Wb As Excel.Workbook, _
               ByVal Wn As Excel.Window)
           If Application.Width > Application.Height Then
           ww.Width = 300: ww.Height = 110
           Else
           ww.Height = 300: ww.Width = 110
           End If
           
           End Sub

    my module is

          Dim myobject As New Class1

          Sub Testare()
           Set myobject.appevent = Application
           If Application.Width > Application.Height Then
           ww.Width = 300: ww.Height = 110
           Else
           ww.Height = 300: ww.Width = 110
           End If
            ww.Show
          End Sub

    where ww is a user form

    with a button I start the event handler and then I load the user form with the correct orientation.

    When I rotate my tablet the orientation of user form change, but it brings to front then VBE windows ( event if the windows was closed before)

    The same problem repeats every time when I change the orientation the device.

    Tuesday, March 31, 2015 6:25 PM
  • I can't test if this will work as I don't have a tablet, if not maybe you can adapt it

    #If VBA7 Then
    Private Declare PtrSafe Function GetDesktopWindow Lib "user32" Alias "GetDesktopWindow" () As LongPtr
    Private Declare PtrSafe Function GetWindowRect Lib "user32" Alias "GetWindowRect" (ByVal hwnd As LongPtr, lpRect As RECT) As Long
    #Else
    Private Declare Function GetDesktopWindow Lib "user32.dll" () As Long
    Private Declare Function GetWindowRect Lib "user32.dll" (ByVal hwnd As Long, ByRef lpRect As RECT) As Long
    #End If
    Private Type RECT
        Left As Long
        Top As Long
        Right As Long
        Bottom As Long
    End Type
    
    Private WithEvents xlApp As Excel.Application
    
    Private Sub xlApp_WindowResize(ByVal Wb As Workbook, ByVal Wn As Window)
        SizeMe
    End Sub
    
    Private Sub UserForm_Initialize()
        Set xlApp = Excel.Application
        SizeMe
    End Sub
    
    Sub SizeMe()
    Dim bLndScp As Boolean Dim res As Long Dim rct As RECT res = GetWindowRect(GetDesktopWindow, rct) With rct bLndScp = (.Right - .Left) >= (.Bottom - .Top) End With If bLndScp Then Me.Width = 300 Me.Height = 100 Else Me.Width = 100 Me.Height = 300 End If End Sub

    If you're only trapping the one application event might as well put it in the userform, it's a class module.

    Could you tell us if/when Excel's Resize event fires when your tablet is rotated, and if it's better to check the Desktop window (as above with the APIs) or Excel's window.


    Tuesday, March 31, 2015 9:19 PM
    Moderator
  • Thank you. I did implemented your  code.  They do work, but they do bring to front VBE’s window (even if closed) before the user form window changes.

    I completed Peter’s code , because the windowresize event wouldn’t start. The only way I found to start the event is by doing this changes :

    Dim aa As New Class1

    Private Sub UserForm_Initialize() Set aa.xlApp = Excel.Application SizeMe End Sub

    I did simulate( change from portrait to landscape and from portrait to landscape), trying it out if the cause isn’t the device by using userform click. When I did that, the userform does change without bringing to front VB windows.  As of now, it seems that it only happens inside the event.

    To the question if it’s better to use Desktop window or Excel’s window , from my personal experience I would use Excel’s window if I wanted to test it on an PC. Both work same on tablet. However by using Excel’s window, I can test it out on my PC as if it was running from a tablet.   

    Wednesday, April 1, 2015 5:03 PM
  • I don't follow. Apart from trapping a resize event what does your Class1 do. what does your Class1Also as I mentioned, you can trap Excel events in the userform, no need to use an extra class unless you are doing other things with Excel's events. 

    Wednesday, April 1, 2015 5:41 PM
    Moderator