Solved: form.width=screen.width

[maryam]

what is the equivalant expression in VBA for form.width=screen.width? I want the width of the form to be the same as the screen width.

[JimmyTheHand]

A more elegant (and comlpicated) solution:

'Declare all the API-specific items Private to the module
Private Declare Function GetSystemMetrics Lib "user32" _
(ByVal nIndex As Long) As Long
Private Const SM_CXSCREEN = 0 'Screen width
Private Const SM_CYSCREEN = 1 'Screen height

Private Declare Function GetDC Lib "user32" _
(ByVal hwnd As Long) As Long

Private Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hDC As Long, ByVal nIndex As Long) As Long

Private Declare Function ReleaseDC Lib "user32" _
(ByVal hwnd As Long, ByVal hDC As Long) As Long

Private Const LOGPIXELSX = 88 'Pixels/inch in X

'A point is defined as 1/72 inches
Private Const POINTS_PER_INCH As Long = 72

'The size of a pixel, in points
Public Function PointsPerPixel() As Double

Dim hDC As Long
Dim lDotsPerInch As Long

hDC = GetDC(0)
lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX)
PointsPerPixel = POINTS_PER_INCH / lDotsPerInch
ReleaseDC 0, hDC

End Function



'The width of the screen, in pixels
Public Function ScreenWidth() As Long
ScreenWidth = GetSystemMetrics(SM_CXSCREEN)
End Function

Sub test()
UserForm1.Width = Int(ScreenWidth * PointsPerPixel)
UserForm1.Show
End Sub

Source of the knowledge is: http://www.awprofessional.com/articles/article.asp?p=366892&rl=1


A less elegant (and complicated) solution:

Sub test()
Application.DisplayFullScreen = True
UserForm1.Width = Application.Width
Application.DisplayFullScreen = False
UserForm1.Show
End Sub
This latter one makes the screen flash once, when Excel goes to fullscreen and back.

Jimmy

[maryam]

dear Jimmy,
I cannot understand functions and these lots of codes, but it works. So many line equivalant to form.width=screen.width!!!!
Thank you very much

[JimmyTheHand]

dear Jimmy,
I cannot understand functions and these lots of codes, but it works. So many line equivalant to form.width=screen.width!!!!
Thank you very much
Dear Maryam,
You are welcome. Unfortunately, (afaik,) Excel doesn't provide such info as screenwidth and height, so you have to utilize functions of the operating system itself, which is the reason so many lines.

In fact, the first solution is not fully clear to me either, (that's why I provided the source,) but I'm content that it works for you too.

Jimmy

[johnske]

Short version:

Standard code module...
Option Explicit

Declare Function GetSystemMetrics32 Lib "User32" _
Alias "GetSystemMetrics" (ByVal nIndex&) As Long


Userform code module...
Option Explicit

Private Sub UserForm_Activate()
With Me
.Left = 0
.Width = GetSystemMetrics32(0) * 0.75
'to set the height = to screen height
'.Top = 0
'.Height = GetSystemMetrics32(1) * 0.75
End With
End Sub

[maryam]

Dear JohnSki,
I cannot write the userform codes under workbook open event. I need to have a form with the screen size when I open Excel.Can u help me with this?

[johnske]

Dear JohnSki,
I cannot write the userform codes under workbook open event. I need to have a form with the screen size when I open Excel.Can u help me with this?I don't understand what you mean by this - are you wantng to show the form when the workbook opens? Use
Option Explicit

Private Sub Workbook_Open()
UserForm1.Show False
End Sub
and put the userform code I gave you above into the userform code module

[lucas]

just call the userform in the workbook open event:
Private Sub Workbook_Open()
UserForm1.Show
End Sub
The form initialize code that John gave you will take over then.

[maryam]

I tried this, form in small size will be shown first in a secend and then full screen size will come. Did you try that elegant version of Jimmy? Isnt that ok?

[johnske]

I tried this, form in small size will be shown first in a secend and then full screen size will come. Did you try that elegant version of Jimmy? Isnt that ok?Well I've got a really slow machine (233MHz) and I don't get any pause like you say... It's the same code as Jimmys' but just with a lot of unnecessary stuff taken out, so it should actually be a little faster, the only real difference is setting the form width before showing it instead of setting it on activate, try

Option Explicit

Private Sub Workbook_Open()
UserForm1.Width = GetSystemMetrics32(0) * 0.75
UserForm1.Show False
End Sub

[mvidas]

I usually just use UserForm1.Width = ActiveWindow.UsableWidthMatt

[maryam]

dear mvidas
what about height? I got debug that object doesnt support this property when I changed to formula to the following:
UserForm1.Height = ActiveWindow.UsableWHeight

[Bob Phillips]

Should be



UserForm1.Height = ActiveWindow.UsableHeight

[maryam]

oh, mistype. yes:
Private Sub Workbook_Open()
UserForm1.Width = ActiveWindow.UsableWidth
UserForm1.Height = ActiveWindow.UsableHeight
UserForm1.Show
End Sub

but the size will be a little bit less than screen size, but it is only on line code:)

[mvidas]

I believe the UsableWidth and UsableHeight give you the width/height of the spreadsheet area, I use that so users can still access the toolbars/etc

If you want it full screen, you should be able to use UserForm1.Height = Application.Height
UserForm1.Width = Application.WidthIf you think the user might not have excel maximized, you could also use Dim AppWS As Long, X As Double, Y As Double
AppWS = Application.WindowState
If AppWS <> xlMaximized Then
Application.ScreenUpdating = False
Application.WindowState = xlMaximized
X = Application.Width
Y = Application.Height
Application.WindowState = AppWS
Application.ScreenUpdating = True
End If
UserForm1.Height = IIf(Y > 0, Y, Application.Height)
UserForm1.Width = IIf(X > 0, X, Application.Width)Matt

[maryam]

thank you Math, I think the first part is enough as excel is generally maximized.