On WB Open, Minimize and Resize Window

[Ark68]

I am hoping someone can help me find a VBA solution, or some references to read through (my initial Google searches handn't revealled any clear examples of what I am looking to do for the most part), that will, on opening a workbook:

1) Minimize it.
2) Set a specific window size (is there a practical way to determine the value needed in resizing, or is it just trial and error?)
3) Disable the ability to maximize or resize

 

[Ark68]

1)

Application.WindowState = xlNormal

2)

    Application.Top = 18
    Application.Left = 1440
    Application.Width = 1431
    Application.Height = 767

3) - Searching - but what I have found sounds scary

I'm unhappy with the positioning and resize. I'd prefer it be dynamic based on the different user's monitor size. The application size is fine, but it would be great if it could be centred somehow on any one user's monitor, regardless of size.

 

[Ark68]

So I found this code in which I applied to my workbook. When I used it verbatim, with the appWidth = maxWidth/16 and maxHeight / 30, this was the result:

Private Sub Workbook_Open()    
    Dim maxWidth As Integer
    Dim maxHeight As Integer

    'Application.WindowState = xlNormal
    'Application.Top = 18
    'Application.Left = 1445
    'Application.Width = 1431
    'Application.Height = 767
    
    Application.WindowState = xlMaximized
    maxWidth = Application.Width
    maxHeight = Application.Height
    Call CenterApp(maxWidth, maxHeight)

End Sub[/code]

Sub CenterApp(maxWidth As Integer, maxHeight As Integer)
    Dim appLeft As Integer
    Dim appTop As Integer
    Dim appWidth As Integer
    Dim appHeight As Integer
    Application.WindowState = xlNormal
    appLeft = maxWidth / 2
    appTop = maxHeight / 2
    appWidth = 1431 'maxWidth / 16
    appHeight = 767 'maxHeight / 30
    Application.Left = appLeft -715
    Application.Top = appTop -384
    Application.Width = appWidth
    Application.Height = appHeight
End Sub

So, I substituted the values of appWidth and appHeight the "preferred" window height and width. This improved things. However, the top left corner of the application was in the centre of the window, which makes sense. This method will provide the flexibility of placement on different size monitors, however, how do I code the adjust ment to move it so that the centre of the sized application window is in the centre of the screen, not the top left corner?

I also noted, that although the size of the app window was correct, the wookbook contents where all focused to the right side. The contents need to be shifted to the left in order to get the first few columns of the worksheet visible (ie columns A:C are out of sight, yet there are a number of empty columns to the left of the window. The user is required to use the horizontal scroll bar to bring columns A:C into view, which, when the horizontal scroll bar has been "removed", is difficult.

 

[Ark68]

Considering a maximized appWidth of 1452 and an appHeight of 792, I made the following changes to my code. The result is the the app window has shifted 715 pts to the left and 384 pts to the top.
So, that works on this monitor, but doesn't solve the compatibility on other monitors. But I think with some clever calculations I should be able to calculate the offset values to replace 715 and 384.

Only hope now is that there is a solution that might disable the ability to resize the app window with the edge handles. Thoughts?