Reset Screen Resolution

I want to give my users the option to reset their screen resolution when they open my application, and also the option to change it back when they're done. I've got the first part working just fine, but my problem is trying to set up for the change back. My macro is below. The two commented lines are what I'm having problems with right now. What I want them to do is put the values for X and Y in Sheet3, but I can't seem to get them to cooperate. I know it's simple, but I'm stuck. Please help.

Thanks,
John

Sub VerifyScreenResolution(Optional Dummy As Integer)
     
    Dim x  As Long
    Dim y  As Long
    Dim MyMessage As String
    Dim MyResponse As VbMsgBoxResult
     
    x = GetSystemMetrics(SM_CXSCREEN)
    y = GetSystemMetrics(SM_CYSCREEN)
    If x = 1361 And y = 768 Then
    Else
        MyMessage = "Your current screen resolution is " & x & " X " & y & vbCrLf & "This program " & _
        "was designed to run with a screen resolution of 1360 X 768 and may not function properly " & _
        "with your current settings." & vbCrLf & "Would you like to change your screen resolution now (you can change it back later, if you wish)?"
        MyResponse = MsgBox(MyMessage, vbExclamation + vbYesNo, "Screen Resolution")
    End If
    If MyResponse = vbYes Then
        ' These lines are not working.  They throw a error 13 Type Mismatch
        'Sheets(Sheet3).Range("A1").FormulaR1C1 = x
        'Sheets(Sheet3).Range("A2").FormulaR1C1 = y
       
        Call Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3")
    End If
     
End Sub

try using:

Sheets(Sheet3).Range("A1").Value = x
Sheets(Sheet3).Range("A2").Value = y

I'm not able to run your code at all - I'm guessing there's a library I don't have loaded. Try converting the data type to long:

Sheets(Sheet3).Range("A1").Value = Clng(x)
Sheets(Sheet3).Range("A2").Value = Clng(y)

Tlafferty,

The data type IS Long; see the DIM statement. However, trying your code, I STILL get the Error 13 Type Mismatch.

Sorry, I forgot there ARE other parts.Add this to your module:

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

and add this to ThisWorkbook

Option Explicit
 
Private Sub Workbook_Open()
     
    Call VerifyScreenResolution
     
End Sub

Your code/workbook should never require a user to alter their desktop to work properly. If it does, you need to redesign it.

Originally Posted by jomili

Romper, that is an arrogant remark!

Perhaps. I am quite arrogant, so it's possible. However, this is a fundamental principle of good design, and one of the few on which I am completely inflexible.

You have no idea of the circumstances in our environment.

I'm not "requiring" my users to do anything. Every user has a wide-screen monitor, but when our IT dept rolled them out they set the display for 800x600. Most of the folks in my area have manually reset them, but not everyone knows how to do that. My application will give them the option to change their setting so the workbook will display properly, and I want to give them the option to change it back when they're done. That second part is what I need help with.

Nothing there changes my opinion. Indeed it reinforces it, as you have designed a workbook knowing that it will not "display properly" for much of your target audience. To me, changing them to fit your design is arrogant.

If you want to help, then please help.

Believe it or not, that is what I am trying to do. Do you reset the resolution if they alt+tab to another app? What if your app crashes?

Ah...didn't see this:
You need to add quotations to the refernce to the sheet and it will work!

        Sheets("Sheet3").Range("A1").FormulaR1C1 = x
        Sheets("Sheet3").Range("A2").FormulaR1C1 = y

Glad to help. Here's a before workbook close macro to add to the ThisWorkbook object:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call ResetScreenResolution
End Sub

Then add this to your current module:

Sub ResetScreenResolution(Optional Dummy As Integer)
     
    Dim x  As Long
    Dim y  As Long
    Dim MyMessage As String
    Dim MyResponse As VbMsgBoxResult
     
    x = Sheets("Sheet3").Range("A1").Value
    y = Sheets("Sheet3").Range("A2").Value
        MyMessage = "Your current screen resolution is " & x & " X " & y & vbCrLf _
        & vbCrLf & "Would you like to change your screen resolution now (you can change it back later, if you wish)?"
        MyResponse = MsgBox(MyMessage, vbExclamation + vbYesNo, "Screen Resolution")

    If MyResponse = vbYes Then
        Sheets("Sheet3").Range("A1").FormulaR1C1 = x
        Sheets("Sheet3").Range("A2").FormulaR1C1 = y
       
        Call Shell("rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,3")
    End If
     
End Sub

Also, if that did the trick for you, please consider marking this thread solved:
To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save

If you're so inclined, you could also add to my reputation by clicking the scales to the right of my user name
Tom

At the risk of aggravating you further, if you have a chart that won't fit into 800x600 then I think you do need a redesign. The idea of a column chart with 500 items on it sounds horrendous to me. However, as you say, I do not know your circumstances so mayhap there is a necessity for it (and I am familiar with the stipulations of management, even if I tend to ignore them).
At the end of the day, you have a solution to your problem so perhaps we should leave it there?

If you only have a few of these low res users, could you not simply go round and assist them to alter their settings if so desired?