Reset Screen Resolution

[jomili]

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

Please Login or Register  to view this content.

[tlafferty]

try using:

Please Login or Register  to view this content.

[jomili]

Same Error 13, Type Mismatch

[tlafferty]

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:

Please Login or Register  to view this content.

[jomili]

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:

Please Login or Register  to view this content.

and add this to ThisWorkbook

Please Login or Register  to view this content.

[romperstomper]

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

[tlafferty]

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

Please Login or Register  to view this content.

[jomili]

Romper, that is an arrogant remark! 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.

If you want to help, then please help.

[jomili]

tlafferty,

How stupid can I be? I'm looking all over the place for what I'm doing wrong, and it's about the most obvious mistake I can make! Thank you so much for spotting that. My macro's working fine now.

Now, would you like to help with the last part? When my users close the workbook, I want an option to popup, telling them the values that their monitor WAS set at (if they changed it) and allowing them to reset it. I think I can do it myself, but am working on another priority. If you want to take a stab that would be great. If not, I appreciate all the help you've provided.

Thanks,
John

[tlafferty]

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

Please Login or Register  to view this content.

Then add this to your current module:

Please Login or Register  to view this content.

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

[jomili]

Thanks Tom.

I changed your code a little. See the completed code below. Thanks for all your help.

Please Login or Register  to view this content.

[romperstomper]

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?

[jomili]

Romper,

Good questions. No, I don't reset the resolution if they alt-tab to another application. My hope is that they WILL alt-tab, and so see how the new resolution helps them to see their screen better. Now, as to the app crashing, I don't know how to resolve that one, other than firing it up again. I've been working on it for a couple of months now (in between other projects), and I haven't had it crash yet. But there's always a first time.

The app is to review budget information. Since the folks reviewing it aren't comfortable with spreadsheets, we have to present the information in a visual format, by creating graphs. My boss requires that all the data (the whole graph) be visible on one screen, with no scrolling. This means that if I have 500 data items that are charted as stacked bar charts, the chart can't extend beyond the screen. Which means I need a lot of screen to put it in. Which means I have to maximize use of the screen, hence the "display properly" issue.

Another way I could tackle this is by changing the Zoom depending on the count of items in the chart, but then I'd have to also resize the fonts and all elements of the chart. That seems pretty complicated to me, but maybe not to you. I'm still pretty new at this.

Regarding

Please Login or Register  to view this content.

the workbook WILL display properly for the majority of my target audience. For a very few it will not, and so for those few I go out of my way to make sure that they can still use it. For those, the first option is to reset their display. The second option is to go back to using scroll bars to see all the data. These are their choices. I don't change anything, I give them the option. If making a tool and giving the users the option of how to use it, after explaining how it can be best used, is arrogant, then I accept the adjective.

If you think it's poor design, you're welcome to think that. It's the best I could think of, with my limited knowledge. If you have a better way, I'm wide open to suggestions.

Thanks,
John

[romperstomper]

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?

[jomili]

Don't worry, you're not aggravating me. I appreciate a call to reexamine my assumptions and operations; in the end it makes for a better product. I would LOVE for someone to show me a better way, 'cause this app is a monster. But I sure can't see a better way.

[romperstomper]

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?

[jomili]

No, that's not really feasible. I'll have maybe 200 users, and out of that maybe 10 will need their resolution changed. But those 10 can be anywhere in the state of Texas, and Texas is a big place to "simply go round" in.