Re-run Macro when user moves workbook from one screen to another?

[gmooney]

Hi there,

I have some code that will automatically zoom a worksheet page to automatically fit the screen on any user's screen. However, when they have a laptop and external monitor and they choose to move the workbook from one monitor to the other the zoom doesn't necessarily fit the screen.

Anyway, to have this particular macro fire whenever the workbook moves from one screen to another?

 

[richh]

Try setting a timer event and checking the Application.Left value. The settings below are set kind of low because I only have one monitor, so you'll have to tinker with the values to meet your needs.

Private Sub Workbook_Open()
    Application.Left = 0
    Application.Top = 0
    theAppPos = 0
    theAppScreen = "Left"
    
    alertTime = Now + TimeValue("00:00:05")
    Application.OnTime alertTime, "CheckAppPosition"
End Sub


'Declare globalVars to retain info while app runs
Global theAppPos As Double
Global theAppScreen As String

Public Sub CheckAppPosition()
    If Application.Left <> theAppPos Then 'The new position does not match oldPos
        theAppPos = Application.Left
    End If
    
    If theAppPos < 200 Then 'the App is on the left
        If theAppScreen <> "Left" Then 'theApp was moved from the right to the left
            theAppScreen = "Left"
            'Execute the update
            MsgBox "The app is left"
        Else 'The app is still on the same side
        End If
    ElseIf theAppPos > 200 And theAppPos < 1200 Then 'the app is on the right
        If theAppScreen <> "Right" Then 'the App was moved from the left to the right
            theAppScreen = "Right"
            'Execute the update
            MsgBox "the app is right"
        Else 'The app is still on the same side
        End If
    Else 'the application is outside the boundaries of both screens
        '<- Mass panic happens here
    End If
    
    alertTime = Now + TimeValue("00:00:05")
    Application.OnTime alertTime, "CheckAppPosition"
            
End Sub

 

[gmooney]

Hi ricch

Would this do anything different if the user moved the workbook from one screen to another in 2 seconds versus 5 minutes?

 

[richh]

The alterTime = Now + TimeValue("00:00:05") lines of code are set to check every five seconds, so it will detect any changes made within the seconds between the last function call. If you set it to five minutes "00:05:00", you can switch around countless times and the code won't execute until that five minute mark.

 

[gmooney]

Hi richh.....how about I try to limit the amount of time this code will run?

Let's say I think the user will only move the workbook from one screen to the other one within a 1 minute time limit?

Or, if this code is not too memory intensive I have no problem letting it continually run.

 

[gmooney]

richh....also do I put this code into ThisWorkbook or into my Module1? I already have some code in ThisWorkbook for Workbook_Open()

 

[richh]

Same as before - if you set it to a minute, the user can change the screen in one second, and keep working for the other 59 seconds. Once the timer hits the next check, it'll run the code. That may or may not impact the reset of your code, and the users' work. That would be up to you to troubleshoot and test to see if the delay would be impactful or potentially create a frustration where the update happens in the middle of someone's work and causes them to loss their progress.

Timed events aren't too strenuous. It really depends on how big your file is and how excessive the function being run is. Again, kinda up to you to see if it fits your build. You could pick apart the code and set it to a button control; then you just tell your users to hit the button when they move screens to run the update. That would eliminate the continuous timer event's resource use.

The code within the Workbook_Open event can just be copy/pasted into your preexisting function. The global definitions and CheckAppPos code go into Module_1 or another module.

 

[gmooney]

Hi ricch

I moved the code to where you said to but I am getting a compile error on the global code about only comments may appear after End Sub, End Function or End Property?

 

[richh]

The Global codes go outside of any subs/functions. Try settingt them at the top of the module before the first sub definition.

 

[gmooney]

ricch

Now I get a Compile error for variable not defined for the alerttime variable?