Disabling Excel Application Resizing

[derny1]

I have an Excel app for which I would like to specify the application window size when the screen resolution is greater than 1280 wide. Setting up the window is the easy part (see code below), but, I can't seem to figure out if it is possible to disable resizing of the window afterwards, whether it be through dragging the borders or minimize/maximize (I am talking about the application and not the workbook itself).

This is a 'dictator' application which uses a worksheet as the interface and not a form, so I cannot set a border property.

I have seen mention of an Api WM_SIZE message function but I am not sure if this can be utilized in VBA.

Here is the code that sets up the applications window size and center's it on the desktop:

'WHEN SM_CXSCREEN IS > 1280, otherwise use separate zoom function

hwnd = FindWindow("XLMAIN", Application.Caption)
Call SetWindowPos(hwnd, HWND_TOPMOST, (GetSystemMetrics(SM_CXSCREEN) - 1250) / 2, (GetSystemMetrics(SM_CYSCREEN) - 760) / 2, 1250, 760, 0)

Thx,
Dennis

 

[Kusaywa]

I've added this to This Workbook.
I'm getting a compile error pointing to the "j" in below code (in RED)
The code works but throws the error.
First part to Zoom sheet, second to go to first open/unlocked cell.

ActiveSheet.Range("A4800:D4800").Select   'set range zoom
ActiveWindow.Zoom = True
Dim rng As Range
    
    Set rng = ActiveSheet.Range("B2:B4800")
    For [COLOR=#ff0000]j[/COLOR] = rng.Column To (rng.Column + rng.Columns.Count - 1)
        For i = rng.Row To (rng.Row + rng.Rows.Count - 1)
            If Cells(i, j).Locked = False And Len(Trim(Cells(i, j).Value)) = 0 Then
                Cells(i, j).Select
                Exit For
            End If
        Next i
    Next j

 

[Kusaywa]

I think I got too much going on in my Workbook to get this to work for me.
At least at my level of knowledge of VBA Code.
To start, my workbook opens to one sheet (Sheet1) and a hidden sheet (Sheet2).
When Sheet1 gets filled to a designated cell (in this case D4800), the sheet is saved to a new workbook.
Sheet2 is then unhidden, copied after Sheet1 as Sheet2 (2) and Sheet2 is re-hidden.
When Sheet2 (2) is full, it's saved, again Sheet2 is unhidden, Sheet2 (3) is copied after Sheet2 (2), Sheet1 is deleted and Sheet2 re-hidden.
This is a continuous process...
Disabling of Resize isn't allowing this to happen.
The Resize "lockdown", I think, has to come off for the procedure to happen.
This, along with Sheets being locked, Workbook locked, Ribbon hidden, Scroll bars hidden...
And that I'm looking to have the Sheets zoom and first empty cell selected...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Sht As Worksheet
   If Target.Address = "$D$4800" And Target.Value <> "" Then
   
   Dim MyPath As String
     Dim MyFileName As String
     
     Application.DisplayAlerts = False
     MyPath = ThisWorkbook.Path
     MyFileName = Left(ThisWorkbook.Name, (InStrRev(ThisWorkbook.Name, ".", -1, vbTextCompare) - 1)) & (" ") & Format(Now, "mm-dd-yyyy hhmm")
     
     If Not Right(MyPath, 1) = "\" Then MyPath = MyPath & "\"
     If Not Right(MyFileName, 4) = ".xlsx" Then MyFileName = MyFileName & ".xlsx"
     
     ActiveSheet.Copy
     
     With ActiveWorkbook
     
         .SaveAs Filename:= _
             MyPath & MyFileName, _
             Password:="peteamy", WriteResPassword:="peteamy", ReadOnlyRecommended:=True _
             
         .Close False
         
         Application.DisplayAlerts = True
         
         End With
   
   ActiveWorkbook.Unprotect "peteamy"
   Application.ScreenUpdating = False
   Sheets("Sheet2").Visible = True
     'Replace "Sheet1" with the name of the sheet to be copied.
     ActiveWorkbook.Sheets("Sheet2").Copy _
        after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
        Sheets("Sheet2").Visible = xlSheetVeryHidden
        
        
        Application.DisplayAlerts = False
 On Error Resume Next
 ActiveSheet.Previous.Previous.Delete
 ActiveWorkbook.Protect "peteamy"
    End If
    
    
        
        End Sub

 

[Jaafar Tribak]

I think I got too much going on in my Workbook to get this to work for me.
At least at my level of knowledge of <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> Code.

Incorporating your own code into the code I posted can be tricky and/or may make your project unstable .. I would recommend to avoid using my code to "lockdown" the excel window unless you feel comfortable with vba.