Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Display Question
My problem is that the User Form is to big to fit on the Screen. It works
good on 1 Computer, but when I use A computer with a smaller Screen it becomes way to big. When that happens 30% of the Form is cut off. Is there a code that I can run on UserForm_Initialize that will size it for the Screen. It would be nice if it would size according to the Display settings. I found this code here in the forumn but I can't get it to work. Sub size_form_and_controls() Dim start_height As Long Dim start_width As Long Dim new_height As Long Dim new_width As Long Dim height_ratio As Double Dim width_ratio As Double Dim ctl As Control With Me 'default settings on your computer start_height = 1024 start_width = 1280 'find current screen resolution new_height = Application.Height new_width = Application.Width 'ratio to apply to form and all Controls height_ratio = new_height / start_height width_ratio = new_width / start_width 'resize the whole form .Height = new_height .Width = new_width 'resize the controls and text For Each ctl In .Controls ctl.Top = ctl.Top * height_ratio ctl.Left = ctl.Left * width_ratio ctl.Height = ctl.Height * height_ratio ctl.Width = ctl.Width * width_ratio 'if control has no font then skip On Error Resume Next ctl.Font.Size = ctl.Font.Size * height_ratio On Error GoTo 0 Next ctl End With |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Display Question
I built this code for the same issue you're having. Just call this procedure
in your userforms Intialize Event. Hope this helps! If so, let me know, click "YES" below. Sub AdjustScreenSize() ' move user form to upper left of screen and show scoll bars With ProductForm ' if form is wider and higher than window width If .Height Application.Height And .Width Application.Width Then ' increae height/width of userform so scroll bar doesn't cover controls .Width = .Width + 15 .Height = .Height + 15 ' add scroll bars .ScrollBars = 3 ' 3 show both hor./ver. bar .ScrollHeight = .Height .ScrollWidth = .Width ' adjust position of userform .Top = Application.Top .Height = ActiveWindow.Height .Left = Application.Left .Width = ActiveWindow.Width Exit Sub End If ' if form is taller than window height If .Height Application.Height Then ' increase height of userform so scroll bar doesn't cover controls .Width = .Width + 15 ' add horizontal scroll bars .ScrollBars = 2 ' 2 show ver. bar .ScrollHeight = .Height ' adjust position of userform .Top = Application.Top .Height = ActiveWindow.Height Exit Sub End If ' if form is wider than window width If .Width Application.Width Then ' increae height of userform so scroll bar doesn't cover controls .Height = .Height + 15 ' add horizontal scroll bars .ScrollBars = 1 ' 1 show hor. bar .ScrollWidth = .Width ' adjust position of userform .Left = Application.Left .Width = ActiveWindow.Width Exit Sub End If End With End Sub -- Cheers, Ryan "Brian" wrote: My problem is that the User Form is to big to fit on the Screen. It works good on 1 Computer, but when I use A computer with a smaller Screen it becomes way to big. When that happens 30% of the Form is cut off. Is there a code that I can run on UserForm_Initialize that will size it for the Screen. It would be nice if it would size according to the Display settings. I found this code here in the forumn but I can't get it to work. Sub size_form_and_controls() Dim start_height As Long Dim start_width As Long Dim new_height As Long Dim new_width As Long Dim height_ratio As Double Dim width_ratio As Double Dim ctl As Control With Me 'default settings on your computer start_height = 1024 start_width = 1280 'find current screen resolution new_height = Application.Height new_width = Application.Width 'ratio to apply to form and all Controls height_ratio = new_height / start_height width_ratio = new_width / start_width 'resize the whole form .Height = new_height .Width = new_width 'resize the controls and text For Each ctl In .Controls ctl.Top = ctl.Top * height_ratio ctl.Left = ctl.Left * width_ratio ctl.Height = ctl.Height * height_ratio ctl.Width = ctl.Width * width_ratio 'if control has no font then skip On Error Resume Next ctl.Font.Size = ctl.Font.Size * height_ratio On Error GoTo 0 Next ctl End With |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Display Question
It works.
I was really hoping to it would resize the user form according to the screen size though. Can that be done? Is it possible to resize the user form according to the screen size? Thanks "Ryan H" wrote: I built this code for the same issue you're having. Just call this procedure in your userforms Intialize Event. Hope this helps! If so, let me know, click "YES" below. Sub AdjustScreenSize() ' move user form to upper left of screen and show scoll bars With ProductForm ' if form is wider and higher than window width If .Height Application.Height And .Width Application.Width Then ' increae height/width of userform so scroll bar doesn't cover controls .Width = .Width + 15 .Height = .Height + 15 ' add scroll bars .ScrollBars = 3 ' 3 show both hor./ver. bar .ScrollHeight = .Height .ScrollWidth = .Width ' adjust position of userform .Top = Application.Top .Height = ActiveWindow.Height .Left = Application.Left .Width = ActiveWindow.Width Exit Sub End If ' if form is taller than window height If .Height Application.Height Then ' increase height of userform so scroll bar doesn't cover controls .Width = .Width + 15 ' add horizontal scroll bars .ScrollBars = 2 ' 2 show ver. bar .ScrollHeight = .Height ' adjust position of userform .Top = Application.Top .Height = ActiveWindow.Height Exit Sub End If ' if form is wider than window width If .Width Application.Width Then ' increae height of userform so scroll bar doesn't cover controls .Height = .Height + 15 ' add horizontal scroll bars .ScrollBars = 1 ' 1 show hor. bar .ScrollWidth = .Width ' adjust position of userform .Left = Application.Left .Width = ActiveWindow.Width Exit Sub End If End With End Sub -- Cheers, Ryan "Brian" wrote: My problem is that the User Form is to big to fit on the Screen. It works good on 1 Computer, but when I use A computer with a smaller Screen it becomes way to big. When that happens 30% of the Form is cut off. Is there a code that I can run on UserForm_Initialize that will size it for the Screen. It would be nice if it would size according to the Display settings. I found this code here in the forumn but I can't get it to work. Sub size_form_and_controls() Dim start_height As Long Dim start_width As Long Dim new_height As Long Dim new_width As Long Dim height_ratio As Double Dim width_ratio As Double Dim ctl As Control With Me 'default settings on your computer start_height = 1024 start_width = 1280 'find current screen resolution new_height = Application.Height new_width = Application.Width 'ratio to apply to form and all Controls height_ratio = new_height / start_height width_ratio = new_width / start_width 'resize the whole form .Height = new_height .Width = new_width 'resize the controls and text For Each ctl In .Controls ctl.Top = ctl.Top * height_ratio ctl.Left = ctl.Left * width_ratio ctl.Height = ctl.Height * height_ratio ctl.Width = ctl.Width * width_ratio 'if control has no font then skip On Error Resume Next ctl.Font.Size = ctl.Font.Size * height_ratio On Error GoTo 0 Next ctl End With |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Display Question
I believe this code should work for what ever screen size you have. Plus, it
will adjust the userform size in case your user has the Excel application not maximized. -- Cheers, Ryan "Brian" wrote: It works. I was really hoping to it would resize the user form according to the screen size though. Can that be done? Is it possible to resize the user form according to the screen size? Thanks "Ryan H" wrote: I built this code for the same issue you're having. Just call this procedure in your userforms Intialize Event. Hope this helps! If so, let me know, click "YES" below. Sub AdjustScreenSize() ' move user form to upper left of screen and show scoll bars With ProductForm ' if form is wider and higher than window width If .Height Application.Height And .Width Application.Width Then ' increae height/width of userform so scroll bar doesn't cover controls .Width = .Width + 15 .Height = .Height + 15 ' add scroll bars .ScrollBars = 3 ' 3 show both hor./ver. bar .ScrollHeight = .Height .ScrollWidth = .Width ' adjust position of userform .Top = Application.Top .Height = ActiveWindow.Height .Left = Application.Left .Width = ActiveWindow.Width Exit Sub End If ' if form is taller than window height If .Height Application.Height Then ' increase height of userform so scroll bar doesn't cover controls .Width = .Width + 15 ' add horizontal scroll bars .ScrollBars = 2 ' 2 show ver. bar .ScrollHeight = .Height ' adjust position of userform .Top = Application.Top .Height = ActiveWindow.Height Exit Sub End If ' if form is wider than window width If .Width Application.Width Then ' increae height of userform so scroll bar doesn't cover controls .Height = .Height + 15 ' add horizontal scroll bars .ScrollBars = 1 ' 1 show hor. bar .ScrollWidth = .Width ' adjust position of userform .Left = Application.Left .Width = ActiveWindow.Width Exit Sub End If End With End Sub -- Cheers, Ryan "Brian" wrote: My problem is that the User Form is to big to fit on the Screen. It works good on 1 Computer, but when I use A computer with a smaller Screen it becomes way to big. When that happens 30% of the Form is cut off. Is there a code that I can run on UserForm_Initialize that will size it for the Screen. It would be nice if it would size according to the Display settings. I found this code here in the forumn but I can't get it to work. Sub size_form_and_controls() Dim start_height As Long Dim start_width As Long Dim new_height As Long Dim new_width As Long Dim height_ratio As Double Dim width_ratio As Double Dim ctl As Control With Me 'default settings on your computer start_height = 1024 start_width = 1280 'find current screen resolution new_height = Application.Height new_width = Application.Width 'ratio to apply to form and all Controls height_ratio = new_height / start_height width_ratio = new_width / start_width 'resize the whole form .Height = new_height .Width = new_width 'resize the controls and text For Each ctl In .Controls ctl.Top = ctl.Top * height_ratio ctl.Left = ctl.Left * width_ratio ctl.Height = ctl.Height * height_ratio ctl.Width = ctl.Width * width_ratio 'if control has no font then skip On Error Resume Next ctl.Font.Size = ctl.Font.Size * height_ratio On Error GoTo 0 Next ctl End With |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
display a user form | Excel Programming | |||
user form question: text box to display result | Excel Discussion (Misc queries) | |||
user form question: text box to display result | Excel Worksheet Functions | |||
How to display =x/y result in a user form | Excel Programming | |||
User Form to display more than 1 reslut | Excel Programming |