Resize UserForm and controls
When you open a workbook with a userform that you create in Excel for Windows you will notice that you think your getting old because the userform will popup very small and you need a optical device.
You can copy the code below in the Userform module of your workbook to change the size automatic when you open the userform on a Mac. Change the 1.5 (50 % larger) in the code if you think it is big or to small.
You can copy the code below in the Userform module of your workbook to change the size automatic when you open the userform on a Mac. Change the 1.5 (50 % larger) in the code if you think it is big or to small.
But what if you want to change the size of a userform/controls and save this settings in the workbook.
You not need the code above to run each time then when you open the userform.
Note: the macro is working in Windows and on the Mac
You can change the properties of the form and all of the controls by hand, no problem if you have a few of them. But not so funny if there are 100 controls or so.
You can use the macro below to do it for you
1: Open the workbook with the userform that you want to change
2: Open the VBA Editor
3: Insert the macro below in a module of the workbook
4: Change this two lines in the code
'You can change the 1.3, use lower then 1 to make it smaller.
Const SizeCoefficient = 1.3
'Enter the name of the UserForm
NameUserform = 'Userform1'
5: Run the macro
6: delete the macro in the module
7. Check out if you like the size now of the UserFom and Controls
8: Save Your workbook if you like it, close without saving if you not like it
If correct the size if your UserForm and Controls are changed now (font size also)
You not need the code above to run each time then when you open the userform.
Note: the macro is working in Windows and on the Mac
You can change the properties of the form and all of the controls by hand, no problem if you have a few of them. But not so funny if there are 100 controls or so.
You can use the macro below to do it for you
1: Open the workbook with the userform that you want to change
2: Open the VBA Editor
3: Insert the macro below in a module of the workbook
4: Change this two lines in the code
'You can change the 1.3, use lower then 1 to make it smaller.
Const SizeCoefficient = 1.3
'Enter the name of the UserForm
NameUserform = 'Userform1'
5: Run the macro
6: delete the macro in the module
7. Check out if you like the size now of the UserFom and Controls
8: Save Your workbook if you like it, close without saving if you not like it
If correct the size if your UserForm and Controls are changed now (font size also)
You can place a breakpoint in the begining of Userformintitialize and execute step-by-step with F8.However I am a bit puzzled why do you call explicitly Userformintitialize; this code is called automatically (like a constructor or initializer of the form) when the user-form loads. I started using this code on my UserForms before Excel 2016 was released (Excel 2016 was the first version in which Mac and Windows user interfaces and VBA components actually looked alike). Allj slots. The Mac version was Excel 2011, and many of the UserForm controls looked awful, especially the MultiPage.
Note: In Excel for Windows 2002-2010 you must have checked this setting:
Trust access to Visual Basic Project (object model)
This is only for the macro above and not for the first example that change the size at runtime.
Office 2003 and Office XP
1.Open the Office 2003 or Office XP application.
2:On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
3.On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box
4.Click OK to apply the setting.
Office 2007
1.Open the 2007 Microsoft Office system application.
2.Click the Microsoft Office button, and then click Application Options.
3.Click the Trust Center tab, and then click Trust Center Settings.
4.Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box
5.Click OK.
6.Click OK.
Office 2010 and 2013
1.Open the 2010 Microsoft Office system application.
2.Click on the File menu, and then click Application Options.
3.Click the Trust Center tab, and then click Trust Center Settings.
4.Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box
5.Click OK.
6.Click OK.
Trust access to Visual Basic Project (object model)
This is only for the macro above and not for the first example that change the size at runtime.
Office 2003 and Office XP
1.Open the Office 2003 or Office XP application.
2:On the Tools menu, click Macro, and then click Security to open the Macro Security dialog box.
3.On the Trusted Sources tab, click to select the Trust access to Visual Basic Project check box
4.Click OK to apply the setting.
Office 2007
1.Open the 2007 Microsoft Office system application.
2.Click the Microsoft Office button, and then click Application Options.
3.Click the Trust Center tab, and then click Trust Center Settings.
4.Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box
5.Click OK.
6.Click OK.
Office 2010 and 2013
1.Open the 2010 Microsoft Office system application.
2.Click on the File menu, and then click Application Options.
3.Click the Trust Center tab, and then click Trust Center Settings.
4.Click the Macro Settings tab, click to select the Trust access to the VBA project object model check box
5.Click OK.
6.Click OK.
Vba Userform Calendar
RawlinsCross
Active Member
Excel Userform Code
Hi folks,
I've research this up the ying-yang and have tried most everything I've found, but still can't seem to fix this problem. When I add items to a userform and add enough so that the scroll bar appears, I can't scroll down and see the last item (or scroll down to the bottom and the bar bounces back up a notch).
Has there been anything added to this solution recently? I've googled it and I get the following strategies.
1. Set .IntegralHeight to False and then to True
2. Reset the .height property in the code
3. Toggle the .MultiSelect property to Single and then to Extended (or to Multi)
Anyhow, none of these seems to have worked for me. Any new advances?
I've research this up the ying-yang and have tried most everything I've found, but still can't seem to fix this problem. When I add items to a userform and add enough so that the scroll bar appears, I can't scroll down and see the last item (or scroll down to the bottom and the bar bounces back up a notch).
Has there been anything added to this solution recently? I've googled it and I get the following strategies.
1. Set .IntegralHeight to False and then to True
2. Reset the .height property in the code
3. Toggle the .MultiSelect property to Single and then to Extended (or to Multi)
Anyhow, none of these seems to have worked for me. Any new advances?