Query Source : Excel Macros Google Group
Solution Type : Forms and VBA
Query by : Kanwaljit
Solution by : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)
Query / Problem:
I have a User Form which needs to take a specified size depending upon the data source. Data Source is in Range DataS1 and DataS2. The following range (named as "Dimensions") shows the dimensions which the User Form needs to have when ever we use DataS1 or DataS2.
UserFormSpec DataS1 DataS2
FormWidth 50 100
FormHeight 40 80
Cell A1 of Sheet1 contains a dropdown containing the names of the datasources, i.e., DataS1 and DataS2. The following code is used to show the UserForm.
Private Sub MyForm_Click()
UserForm.Show
End Sub
What is wish is that when I select DataS1 or DataS2 from Cell A1, the UserForm width and height changes according to the numbers in range "Dimensions".
Solution:
Brief Description: Solution requires use of Event Macros, VBA, Forms, Object Hierarchy. Even if you don't know refer below to solve such problems in a very easy manner.
As you want to re-size the form whenever value in Cell A1 of Sheet1 changes, you mustuse event macro to achieve this. Since event macros are automatically fired in response to associated events. Following are some examples of excel events for which event macros are already in the MS Excel applications:
Name of Event | Event Macro
1. Opening of Workbook | Workbook_Open()
2. Double click a cell | Worksheet_BeforeDoubleClick
3. Activate a Sheet | Worksheet_Activate()
4. Pivot Table Update | Worksheet_PivotTableUpdate
5. Change Cell Value | Worksheet_SelectionChange
etc etc etc
Since, each event macro is associated with an event, it is also associated with a particular object. Like, if defined then a workbook event macro is associated to the workbook where it is defined. So if you've written a workbook event macro in Workbook A then it won't affect Workbook B. Same for Sheets, pivot tables etc.
Full Solution to the Query:
1. Open the desired Excel file.
2. Go to VBE by pressing Alt+F11.
3. Under Microsoft Excel Objects for this workbook, Double Click on Sheet1 to open it's code window.
4. Paste the Following Code in it. (Change the User form name from UserForm1 to your form name)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
If UCase(Target.Value) = "DATAS1" Then
UserForm1.Height = 40
UserForm1.Width = 50
ElseIf UCase(Target.Value) = "DATAS2" Then
UserForm1.Height = 80
UserForm1.Width = 100
End If
End If
End Sub
5. Test your Macro by changing the value of Cell A1.
Example:
Comments