Workbook Resizing - How To Prevent ?

[Logit]

Can anyone point me to an example of how to prevent the workbook from being resized when opened ?

My project has a set dimension for functionality.

Thank you !

[Greg M]

Hi there,

Try inserting the following code into the ThisWorkbook VBA CodeModule of your workbook and see if it does what you need:

Please Login or Register  to view this content.

The highlighted values can be altered to suit your own requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Logit]

Greg

The code sets the workbook to the correct size but doesn't stop anyone from resizing the window or the workbook manually.

I've done alot of searching today for something that will "freeze" the workbook/window ... no luck.

[TMS]

You could try protecting the structure of the workbook with a password.

[Greg M]

Hi again,

Many thanks for your feedback.

Does the following code help? It doesn't prevent a User from resizing the window manually, but it undoes his/her resizing immediately afterwards.

As before, the code should be inserted in the ThisWorkbook VBA CodeModule.

Please Login or Register  to view this content.

Alter these values to suit your requirements.



Hope this helps - as always, please let me know how you get on.

Regards,

Greg M

[Logit]

Hey Greg:

It works great on the worksheet but the Main Workbook is still resizable.

Is there a means of applying that code to the WorkBook instead of the WorkSheet ?

Thank you.

[mikerickson]

Have you looked at the Workbook_WindowResize event in the ThisWorkbook code module?

[Logit]

Im still learning.

What should I do with

Please Login or Register  to view this content.

I believe you may be referring to a change in the syntax but I'm not certain how to proceed.

[Greg M]

Hi again,

I'm not really sure what you mean by:

It works great on the worksheet but the Main Workbook is still resizable.

When I open the workbook I can drag the corners/edges of the Excel window to resize it, but as soon as I release the mouse button the VBA code immediately resets the window to the dimensions specified in the code.


Regards,

Greg M

[Logit]

It doesn't work that way for me. I'm probably using the wrong terms but I'll try to explain what occurs on my system.

Excel has the main window or workbook. Then within that main frame is another window or the "sheet" that you work with. The sheet resizes back to its original dimensions, but the main window/workbook can still be expanded in any direction. It does not shrink back.

Is there a way to ammend the code so the main window automatically resizes as well ?

Or - should the code as presented, already do that ? If so, I wonder what is going on with my system.

[Greg M]

Hi again,

Sorry about the delay in getting back to you.

I'm running Windows 7 and Office 2013.

I don't have a "frame within a frame" representing the worksheet, just an overall window (on the Desktop) for the Excel application. As mentioned previously, if the User tries to resize the overall window, the VBA code will reset the dimensions of that window to those specified in the code.

Regards,

Greg M

[NoSparks]

Two different things in play here.
... the application ie: Excel, which has the bigger X in upper right corner of the Excel application window
... the application document ie: the workbook, which has a smaller X, below the other, inside the application window


Perhaps Andrew Poulsom's posts #6 & #10 in this discussion will be of use.

The thread here looks to have similar issues to what you currently have. I found the last post very telling as to who would know how to handle things.

And that leads to this and this, unfortunately the file link is ancient.


Hope this posts, been getting Server too busy, try again later all day.

[NoSparks]

posted twice

[Logit]

NoSparks:

Thank you for digging up this information. I marvel how you are able to locate these things.

See ya friend !



GregM:

Thank you for your assistance. I'm using Excel 2007 and Win 10.
Have a great day !

[Greg M]

Hi again,

Many thanks for all of your feedback and also for the Reputation increase - much appreciated

Confusion cleared - Excel 2013 doesn't have a Minimize Workbook "X" button, just a Minimize Application "X" button.

Best regards,

Greg M