Restricting Window Size by Range

[jonvanwyk]

I have created an electronic timesheet that is nearly complete. I have stripped the form of all toolbars, ribbons, etc that the user will not need. I would now like to restrict the window size of the entire workbook by range A1 through O31. I DO NOT want to prevent minimizing the document to the Taskbar, but I DO want to prevent resizing to include maximize.

(see attached screenshot of my form at the size I want to restrict it to)

[snb]

Sub snb()
    With ThisWorkbook.Sheets(1)
        .Columns(16).Resize(, Columns.Count - 15).Hidden = True
        .Rows(32).Resize(Rows.Count - 31).Hidden = True
        .ScrollArea = "A1:O31"
    End With
End Sub

[jonvanwyk]

Sub snb()
    With ThisWorkbook.Sheets(1)
        .Columns(16).Resize(, Columns.Count - 15).Hidden = True
        .Rows(32).Resize(Rows.Count - 31).Hidden = True
        .ScrollArea = "A1:O31"
    End With
End Sub

I attempted your solution and it seemed to have no affect. I have attached to this reply a screenshot of how I entered the code.

[snb]

Why don't you put it into the workbook_open Event ?

[jonvanwyk]

Why don't you put it into the workbook_open Event ?

I have done this, and it is still not working (see attached screenshot)

[snb]

You have my permission to adapt the code to your specific situation, e.g.

With ThisWorkbook.Sheets("Menu")
  .Columns(16).Resize(, Columns.Count - 15).Hidden = True
  .Rows(32).Resize(Rows.Count - 31).Hidden = True
  .ScrollArea = "A1:O31"
End With

Code doesn't work when macros have been disabled.

[pike]

Hi jonvanwyk
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

[jonvanwyk]

Hi jonvanwyk
Don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

I do not see where I have quoted whole posts, or created clutter. This post seems pretty straight forward to me. Also, I have had two different suggestions in the same post. If I do not quote, others might be confused which solution I am discussing. Rather than cost me "2 points", perhaps your energies can be better utilized in helping me solve my issue. Then I can mark it "solved" and move on :-)

[royUK]

Is this what you want? You need to adjust the sizes

Option Explicit

Sub resize()
With Application
   .WindowState = xlNormal
   .Left = 450
   .Top = 1
   .Width = 750
   .Height = 300
End With
End Sub

[jonvanwyk]

Is this what you want? You need to adjust the sizes

Option Explicit

Sub resize()
With Application
   .WindowState = xlNormal
   .Left = 450
   .Top = 1
   .Width = 750
   .Height = 300
End With
End Sub

I tried using the code above to no avail. Did I enter it incorrectly? (see attached) It tells me there is an issue with "Option Explicit" being between subs. My width will be 837 and the height 622. What does "Left" and "Top" represent?

[pike]

Hi jonvanwyk
You have quoted back every answer that you have received
To post a reply use the Quick Reply window at the bottom of the thread.
dont click on the Quote button

[Leith Ross]

Hello jonvanwyk,

I suspect you are not calling the macro either at the right time or in the right place. This code needs to be called in the UserForm's Activate event. Here is where you should place the call...

Private Sub UserForm_Activate()
    Call RemoveMaximize Application.hWnd
End sub

[royUK]

It's not necessary to quote whole posts, simply refer to the poster, we all have names.

There's no point keep attaching images. We can't check what you are doing without the workbook

You are receiving free help, so we expect you to follow our rules and any requests from moderators concerning your posts.

[jonvanwyk]

I have decided not to specify window size this way. Thank you for attempting to provide a solution. I will begin a new post with a different idea.