Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
I have a Multi Page User Form that I need to be able to resize according to
Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
UserForms have Top, Left, Width and Height properties that allow you to
position and size them with code during the initialize event. All are measured in pixels so: Private Sub UserForm_Initialize() With Me .Top = 25 .Left = 25 .Height = 500 .Width = 800 End With End Sub The above would take up most of the screen. But it is only an example of how to use it. "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Hi Brian, I tested this and it might suit your puirpose more that the
previous code. Private Sub UserForm_Initialize() Me.Height = Application.UsableHeight Me.Width = Application.UsableWidth End Sub "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
I tried it. I see how to adjust the height and width, but the others i didn't
see any change. Thiis adjust the Outer Main User Form Window, but the User Form is still the same size. Is there a way to automate this process to adjust both? ..Top = Adjust what? I changed it but nothing happens ..Left = Adjusts What? I changed it but nothing happens ..Height = Adjusts the User Form Window Height ..Width = Adjusts the User Form Window Width "JLGWhiz" wrote: UserForms have Top, Left, Width and Height properties that allow you to position and size them with code during the initialize event. All are measured in pixels so: Private Sub UserForm_Initialize() With Me .Top = 25 .Left = 25 .Height = 500 .Width = 800 End With End Sub The above would take up most of the screen. But it is only an example of how to use it. "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Tried it too. 1/3 of the User Form is cut off.
How can I get the Min/Max Buttons on the User Form? "JLGWhiz" wrote: Hi Brian, I tested this and it might suit your puirpose more that the previous code. Private Sub UserForm_Initialize() Me.Height = Application.UsableHeight Me.Width = Application.UsableWidth End Sub "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Afraid the max/min buttons are beyond my programming expertise. There are
no ready-made controls to put them on a UserForm that I know of. It might be possible with some advanced programming to go into the source code and extract the icons and functions to do it, but I am not that astute. "Brian" wrote in message ... Tried it too. 1/3 of the User Form is cut off. How can I get the Min/Max Buttons on the User Form? "JLGWhiz" wrote: Hi Brian, I tested this and it might suit your puirpose more that the previous code. Private Sub UserForm_Initialize() Me.Height = Application.UsableHeight Me.Width = Application.UsableWidth End Sub "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Here is some code with a user's comments that I copied off the web. As you
can see it goes into the application files to employ the controls. =============== #1 02-26-2005, 11:06 AM RB Smissaert Guest Posts: n/a Minimize and maximize buttons userforms -------------------------------------------------------------------------- Please Register to Remove these Ads How do I add minimize and maximize buttons to a VBA userform? I have this code that does it, but when I change the caption of the userform the buttons don't work anymore. I am slowly coming to the conclusion that it probably is not worth the trouble and that it is better to make your own buttons. Public Declare Function FindWindow _ Lib "user32" Alias _ "FindWindowA" (ByVal lpClassName As Long, _ ByVal lpWindowName As Long) Private Declare Function SetWindowLong _ Lib "user32" _ Alias "SetWindowLongA" (ByVal hwnd As Long, _ ByVal nIndex As Long, _ ByVal dwNewLong As Long) As Long Private Sub UserForm_Initialize() Dim hWnd as Long hWnd = FindWindow(vbNullString, UserForm1.Caption) SetWindowLong hWnd, -16, &H20000 Or &H10000 Or &H84C80080 End Sub "Brian" wrote in message ... Tried it too. 1/3 of the User Form is cut off. How can I get the Min/Max Buttons on the User Form? "JLGWhiz" wrote: Hi Brian, I tested this and it might suit your puirpose more that the previous code. Private Sub UserForm_Initialize() Me.Height = Application.UsableHeight Me.Width = Application.UsableWidth End Sub "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Thiis adjust the Outer Main User Form Window, but the User
Form is still the same size. Is there a way to automate this process to adjust both? I'm not sure what you are saying or asking here. Do you want to increase the size of the controls on the userform as well? Are you expecting the spacing between controls to adust? What? At any rate, I believe that to get any other results each individual object would have to be addressed. "Brian" wrote in message ... I tried it. I see how to adjust the height and width, but the others i didn't see any change. Thiis adjust the Outer Main User Form Window, but the User Form is still the same size. Is there a way to automate this process to adjust both? .Top = Adjust what? I changed it but nothing happens .Left = Adjusts What? I changed it but nothing happens .Height = Adjusts the User Form Window Height .Width = Adjusts the User Form Window Width "JLGWhiz" wrote: UserForms have Top, Left, Width and Height properties that allow you to position and size them with code during the initialize event. All are measured in pixels so: Private Sub UserForm_Initialize() With Me .Top = 25 .Left = 25 .Height = 500 .Width = 800 End With End Sub The above would take up most of the screen. But it is only an example of how to use it. "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Hi Brian,
Changing the userform size does not appear to change the size of the controls within the userform. You need to use Zoom for that. The following code tests for the video resolution and sets the useform Height, Width and Zoom accordingly. However, some caveats. Video resolution Height and Width is not proportional for all resolutions so there is some discreprencies in the calculations, particulary with the Zoom, but it depends to some extent how large you want the userform in relation to the usable screen size so you will need to do some testing. Note the comments. The API declaration must be at the top of the module before any other subs. You will need to set the values depending on the resolution of the screen on which the userform was developed. 'Video display code from the following link 'http://spreadsheetpage.com/index.php/site/tip/determining_the_users_video_resolution/ 'API declaration (At top of module) Declare Function GetSystemMetrics32 _ Lib "user32" _ Alias "GetSystemMetrics" _ (ByVal nIndex As Long) As Long Public Const SM_CXSCREEN = 0 Public Const SM_CYSCREEN = 1 Sub Show_Userform() Dim vidWidth As Long Dim vidHeight As Long Dim dblMultWdth As Double Dim dblMultHt As Double Dim dblZoom As Double vidWidth = GetSystemMetrics32(SM_CXSCREEN) vidHeight = GetSystemMetrics32(SM_CYSCREEN) '1152 and 864 is initial setup resolution 'Edit to your setup screen resolution dblMultWdth = vidWidth / 1152 dblMultHt = vidHeight / 864 dblZoom = (dblMultWdth + _ dblMultHt) / 2 'Average With UserForm1 'Lookup in Help for other options 'for StartUpPosition. Needs to be manual 'for setting left and top positions. .StartUpPosition = 0 .Zoom = 100 * dblZoom .Width = .Width * dblMultWdth .Height = .Height * dblMultHt .Top = 200 .Left = 100 .Show End With End Sub -- Regards, OssieMac |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
All this code goes in a module, then I reference it from the User Form Int?
Can you help me set this up correctly please? "OssieMac" wrote: Hi Brian, Changing the userform size does not appear to change the size of the controls within the userform. You need to use Zoom for that. The following code tests for the video resolution and sets the useform Height, Width and Zoom accordingly. However, some caveats. Video resolution Height and Width is not proportional for all resolutions so there is some discreprencies in the calculations, particulary with the Zoom, but it depends to some extent how large you want the userform in relation to the usable screen size so you will need to do some testing. Note the comments. The API declaration must be at the top of the module before any other subs. You will need to set the values depending on the resolution of the screen on which the userform was developed. 'Video display code from the following link 'http://spreadsheetpage.com/index.php/site/tip/determining_the_users_video_resolution/ 'API declaration (At top of module) Declare Function GetSystemMetrics32 _ Lib "user32" _ Alias "GetSystemMetrics" _ (ByVal nIndex As Long) As Long Public Const SM_CXSCREEN = 0 Public Const SM_CYSCREEN = 1 Sub Show_Userform() Dim vidWidth As Long Dim vidHeight As Long Dim dblMultWdth As Double Dim dblMultHt As Double Dim dblZoom As Double vidWidth = GetSystemMetrics32(SM_CXSCREEN) vidHeight = GetSystemMetrics32(SM_CYSCREEN) '1152 and 864 is initial setup resolution 'Edit to your setup screen resolution dblMultWdth = vidWidth / 1152 dblMultHt = vidHeight / 864 dblZoom = (dblMultWdth + _ dblMultHt) / 2 'Average With UserForm1 'Lookup in Help for other options 'for StartUpPosition. Needs to be manual 'for setting left and top positions. .StartUpPosition = 0 .Zoom = 100 * dblZoom .Width = .Width * dblMultWdth .Height = .Height * dblMultHt .Top = 200 .Left = 100 .Show End With End Sub -- Regards, OssieMac |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
I bet if you thought about it you could do it. I have a fear of the unknown,
so API setting are intimadating to me, but I am sure you at lease know what they are and how they can effect your computer. I am not that good at this VBA stuff yet, but I am learning more and more everyday thanks to some excelent help from people like yourself. You guys really have a good grasp on this stuff. I want to thank you for all your help. "JLGWhiz" wrote: Afraid the max/min buttons are beyond my programming expertise. There are no ready-made controls to put them on a UserForm that I know of. It might be possible with some advanced programming to go into the source code and extract the icons and functions to do it, but I am not that astute. "Brian" wrote in message ... Tried it too. 1/3 of the User Form is cut off. How can I get the Min/Max Buttons on the User Form? "JLGWhiz" wrote: Hi Brian, I tested this and it might suit your puirpose more that the previous code. Private Sub UserForm_Initialize() Me.Height = Application.UsableHeight Me.Width = Application.UsableWidth End Sub "Brian" wrote in message ... I have a Multi Page User Form that I need to be able to resize according to Monitor Size and Resolution? I saw on Chip Pearsons site some code for resizing a User form, but I am not sure how to use it. I don't want to possibly mess up my computer settings. It would be nice if my User Form would have a Min, Max & Close button like most Windows apps have. Is it possible to get the User Form to Automaticaly resize according to Monitor size and resolution? . . |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Have you ever seen the code on Chip Pearsons site for doing this? It has
provisions for Min/Max/Resizing Control etc... My problem is that the code is very intimadating to me because I don't know enough about it. I am sure it works well, but I wouldn't have a clue how to even set it up let alone trouble shoot it. http://www.cpearson.com/excel/formcontrol.aspx "OssieMac" wrote: Hi Brian, Changing the userform size does not appear to change the size of the controls within the userform. You need to use Zoom for that. The following code tests for the video resolution and sets the useform Height, Width and Zoom accordingly. However, some caveats. Video resolution Height and Width is not proportional for all resolutions so there is some discreprencies in the calculations, particulary with the Zoom, but it depends to some extent how large you want the userform in relation to the usable screen size so you will need to do some testing. Note the comments. The API declaration must be at the top of the module before any other subs. You will need to set the values depending on the resolution of the screen on which the userform was developed. 'Video display code from the following link 'http://spreadsheetpage.com/index.php/site/tip/determining_the_users_video_resolution/ 'API declaration (At top of module) Declare Function GetSystemMetrics32 _ Lib "user32" _ Alias "GetSystemMetrics" _ (ByVal nIndex As Long) As Long Public Const SM_CXSCREEN = 0 Public Const SM_CYSCREEN = 1 Sub Show_Userform() Dim vidWidth As Long Dim vidHeight As Long Dim dblMultWdth As Double Dim dblMultHt As Double Dim dblZoom As Double vidWidth = GetSystemMetrics32(SM_CXSCREEN) vidHeight = GetSystemMetrics32(SM_CYSCREEN) '1152 and 864 is initial setup resolution 'Edit to your setup screen resolution dblMultWdth = vidWidth / 1152 dblMultHt = vidHeight / 864 dblZoom = (dblMultWdth + _ dblMultHt) / 2 'Average With UserForm1 'Lookup in Help for other options 'for StartUpPosition. Needs to be manual 'for setting left and top positions. .StartUpPosition = 0 .Zoom = 100 * dblZoom .Width = .Width * dblMultWdth .Height = .Height * dblMultHt .Top = 200 .Left = 100 .Show End With End Sub -- Regards, OssieMac |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Hi Brian,
Yes. The code goes in a standard module. It was designed to be the code that shows the userform so whatever code you are using to show the userform, you need to incorporate it with it. If necessary you can call it from other code with the following line of code. Call Show_Userform Without seeing you entire project it is difficult to help with how you need to set it up with your project. I'm declining to get involved with Chip Pearsons's code. -- Regards, OssieMac |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
I am gett an error message on the
vidWidth = GetSystemMetrics32(SM_CXSCREEN) Run time erro 453 Can't find DLL entry point GetSystemMetrics32 in user 32 I am about to give up on this. "OssieMac" wrote: Hi Brian, Yes. The code goes in a standard module. It was designed to be the code that shows the userform so whatever code you are using to show the userform, you need to incorporate it with it. If necessary you can call it from other code with the following line of code. Call Show_Userform Without seeing you entire project it is difficult to help with how you need to set it up with your project. I'm declining to get involved with Chip Pearsons's code. -- Regards, OssieMac |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do you resize a Multi Page User Form?
Hi Brian,
Public declarations must be at the TOP of a STANDARD module and you cannot change the Public declarations to Private. Therefore the code below goes at the top of a standard module and do not change anything in the declarations. (I have now included STANDARD module in the comment.) I would not place the remainder of the code in a Worksheet_SelectionChange event because if you do that it will run as soon as you click a cell on the worksheet. (or perhaps that is what you want it to do). I think it would be preferrable to place a command button on the worksheet and attach the code to the button. Basically it is the Userform Show code and Userform1.Show must be the last command in the Sub. (In my code it is incorporated in a With / End With but it is still the last executable line of code.) Option Explicit 'API declaration (At top of STANDARD module) Declare Function GetSystemMetrics32 _ Lib "user32" _ Alias "GetSystemMetrics" _ (ByVal nIndex As Long) As Long Public Const SM_CXSCREEN = 0 Public Const SM_CYSCREEN = 1 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating multi-user Excel form for online | Excel Discussion (Misc queries) | |||
Hiding some pages on multi page form | Excel Programming | |||
User form grid - resize / font size | Excel Programming | |||
Why does Listview control shift to top on multi-page form? | Excel Programming | |||
how do i set up multi page user form | Excel Discussion (Misc queries) |