Size a UserForm ...

[Rodie]

Hi all ...

I use a UserForm on VBA (Excel). When I initialize this UserForm, I size it like that :

Private Sub UserForm_Initialize()
  Me.Width = 1100
  Me.Height = 800
    ...
End Sub

The problem is that another computer won't have the same screen area (1152x854, 1024x768 pixels ...) So, another computer won't see the entire Userform. Is there a way to size it according to the screen area (for example 2/3 of the area), and not define values like me ???

Thanks a lot in advance.
Rodie

 

[Rodie]

Thanks Loomah for your replies.
I posted this topic 2 times (by mistake, because of a problem on this site ...) and unfortunately, the one where there were your answers has been deleted. Here your suggestion.

Dim dHi As Double
Dim dWide As Double
Dim dHi2 As Double
Dim dWide2 As Double
Dim ctrl As Control
    With Me
            'store initial height/width
        dHi = .Height
        dWide = .Width
            'set new height/width
        .Height = Application.UsableHeight
        .Width = Application.UsableWidth
            'Set proportions for height/width
        dHi2 = .Height / dHi
        dWide2 = .Width / dWide
            'apply to each cntrol on form
        For Each ctrl In .Controls
            With ctrl
                .Top = .Top * dHi2
                .Left = .Left * dWide2
                .Height = .Height * dHi2
                .Width = .Width * dWide2
            End With
        Next
    End With
End Sub

It works very well. Thanks !!!
But just one thing. On my computer, is it normal that UsableWidth effectively corresponds to the screen Width, but not UsableHeight ?
That is to say :
UsableWidth = Screen.Width
but UsableHeight = Screen.Heigth / 1.3 (approximatively) ...

Anyway, Thanks a lot to you
Rodie

 

[Loomah]

Hi
I'm not 100% sure exactly what it means but it's basically the height of the application that is usable!! This means that it excludes things like the title bar, commandbars, status bar and, strangely, the formula bar. If you want to use the full height & width of the app then just use the height & width properties.

You should note that both are relative to the application window and if this is not maximised then the userform will fit proportionately into the window size.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[Rodie]

You are totally right !

The UsableHeight is exactly the one of the application. Indeed, the UserForm fits perfectly to the excel cells total height in background of the UserForm.

Thanks a very lot Loomah. Have a nice day.
Rodie