Fixing Window Size & Position

[GreenthumbUK]

Hi all,
1.I have a slight problem, is it possible to fix the size of the excel file windows when opened, this is so i can design a standard form and make sure it fits on the page regardless of screen res.

2.can i hid the scroll bars & function?

thanks

 

[RobSchultz]

    With Application.Windows("Book1")
        .WindowState = xlNormal
        .Width = 400
        .Height = 300
    End With
    
    With Application
        .DisplayScrollBars = False
        .DisplayFormulaBar = False
    End With

You will have to use some API calls to change the size of the actual Excel application window.

HtH,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[fumei]

There is also the hard way. Make forms for each common resolution, check for current screen resolution right off, and use the appropriate one. It wasn't that bad as realistically there are not many possibilities.

It was easier than writing code to resize every object on the fly. I manually resized the controls on each form. The actual code was the same for each - cut and paste.

I did this a while back except for anyone using 640x480, in which case I responded with a message box saying "bleeech blach - no way."






Gerry

 

[GreenthumbUK]

Thats very cool, how about disbaleing the scroll function on a mouse wheel?

 

[fumei]

You can use the code Rob posted to hide the scroll bars on the application iyself. I am unclear what scroll bars you are talking about. If your forms fit the resolution - why would there be scrolls? Unless you have control objects that have them. Which is another thing.

Gerry

 

[jajinder]

Hi, this is a code I use for a userform to do so... Perhaps u can use it...
In a class-mod

Option Explicit

' 32-bit API declaration
Private Declare Function GetSystemMetrics32 Lib "user32" _
Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

' 16-bit API declaration
Private Declare Function GetSystemMetrics16 Lib "user" _
Alias "GetSystemMetrics" (ByVal nIndex As Integer) As Integer

Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1

Property Get GetCurrentWidth()
Dim vidWidth As Integer
If Left(Application.Version, 1) = 5 Then
' 16-bit Excel
vidWidth = GetSystemMetrics16(SM_CXSCREEN)
Else
' 32-bit Excel
vidWidth = GetSystemMetrics32(SM_CXSCREEN)
End If
GetCurrentWidth = vidWidth
End Property

Property Get GetCurrentHeight()
Dim vidHeight As Integer
If Left(Application.Version, 1) = 5 Then
' 16-bit Excel
vidHeight = GetSystemMetrics16(SM_CYSCREEN)
Else
' 32-bit Excel
vidHeight = GetSystemMetrics32(SM_CYSCREEN)
End If
GetCurrentHeight = vidHeight
End Property

In UserForm

Private Sub UserForm_Initialize()

Dim j As Integer
Dim Reso As New Klasse1

With Me
.Top = 0
.Left = 0
.Height = (Reso.GetCurrentHeight / 4) * 3
.Width = (Reso.GetCurrentWidth / 4) * 3
End With

With Me
For j = 0 To .Controls.Count - 1
With .Controls(j)
.Top = .Top * Reso.GetCurrentHeight / 600
.Height = .Height * Reso.GetCurrentHeight / 600
.Left = .Left * Reso.GetCurrentHeight / 600
.Width = .Width * Reso.GetCurrentHeight / 600
.Font.Size = Int(.Font.Size * Reso.GetCurrentHeight / 600)
End With
Next
End With
End sub

Good Luck.....
jajinder



Greets Jajinder

 

[jajinder]

by the way... Does not look very nice with 640*480.
The code resizes every object on the fly Gerry. I think that this one is easyer then yours.. : )

Greets Jajinder

 

[fumei]

As far as I'm concerned nothing looks good at 640x480. Mind you, I still play around with the original Sokoban....in CGA - for those that remember such a thing as CGA.

Thanks Jajinder! Another piece of good stuff from here. Love this place. I am going to play with your code. In particular I want to see how well it works with the many controls I have on a multi-tab that has more multi-tabs on it. Something that resizes everyuthing properly on the fly would be great.



Gerry

 

[jajinder]

I could send you a sample of my programm, so you could see that it resizes every controle...

Greets Jajinder