catalogue
topic
As shown in the figure, this table is the payroll and employee file table of a company. When viewing the payroll, you want to be able to double-click the employee's name to query the employee's basic information in the employee file table, and temporarily create a window to display the employee's information.
| month | Work area | department | full name | base pay | bonus | subsidy | Attendance | Absence (days) | rest | Overtime hours | Overtime amount | Deduct other expenses | Wages payable |
| 1900/1/8 | Administration Department | Administration Department | Zhang San | 1100 | 2000 | 500 | 176 | 0 | 9 | 0 | 0 | 8 | 3592 |
| 1900/1/8 | Administration Department | Administration Department | Li Si | 1100 | 2000 | 500 | 176 | 0 | 9 | 0 | 0 | 8 | 3592 |
| 1900/1/8 | Administration Department | Administration Department | WangTwo | 1100 | 2000 | 500 | 176 | 0 | 9 | 0 | 0 | 8 | 3592 |
| full name | post | department | region | Employment time | Term of validity | Age | Is it a Shenzhen account | Gender | nation | ID number | date of birth | contact number | Native place | registered residence |
| Zhang San | executive director | Administration Department | Administration Department | 55 | Non Shenzhen account | male | Chinese | 515024196602154156 | 1966/2/15 | XXXXX | Sichuan | Sichuan | ||
| WangTwo | executive director | Administration Department | Administration Department | 55 | Non Shenzhen account | male | Chinese | 516024196602154156 | 1966/2/15 | XXXXX | Sichuan | Sichuan |
code
Use the Add method of VB component collection object (VBComponents) to Add a form component, use the Properties property of the form to set each property of the form, use its Designer property to access the Designer, and then Add a control to display all information.
Add a reference to the VBE library "Microsoft Visual Basic for Applications Extensibility 5.3", as shown in the following figure:
Step 1 Press the key combination [Alt+F11] to open VBE.
Step 2 Double click sheet1 and enter the following code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim i As Long
If Target.Column = 4 And Target.Row > 1 And Target.Row <= Range("A1").CurrentRegion.Rows.Count Then
Dim arrHeader
Dim arrData
Dim usForm As Object
Dim objLbl As Object
Dim Rng As Range
Cancel = True
Set Rng = Sheet2.Range("A:A").Find(Target.Value)
If Rng Is Nothing Then
Exit Sub
End If
arrHeader = Sheet2.Range("A1:O1").Value
arrData = Intersect(Rng.EntireRow, Sheet2.Range("A:O")).Value
Set usForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
With usForm
.Properties("Caption") = Target.Value & " archives"
.Properties("Height") = 18 * (UBound(arrData, 2) + 2)
.Properties("Width") = 420
With .Designer
For i = 1 To UBound(arrData, 2)
Set objLbl = .Controls.Add("Forms.Label.1")
With objLbl
.Top = 12 + 18 * (i - 1)
.Height = 12
.Width = 100
.Left = 18
.Caption = arrHeader(1, i) & ":"
End With
Set objLbl = .Controls.Add("Forms.Label.1")
With objLbl
.Top = 12 + 18 * (i - 1)
.Height = 12
.Width = 300
.Left = 130
.Caption = arrData(1, i)
End With
Next i
End With
End With
VBA.UserForms.Add(usForm.Name).Show
ThisWorkbook.VBProject.VBComponents.Remove usForm
End If
End SubStep 3 Return to the worksheet interface, select the Development Tools tab in the ribbon, click macro security in the code group, and check trust access to VBA project object model in the macro settings option, as shown in the figure.
Step 4 Double click the name column in payroll. When it exists in employee file table, the window will be displayed, as shown in the figure. When the form is closed, the workbook is not saved.
VBComponents collection
When you need to access the VBA project of a workbook, you can access the project object through the VBProject property. The VBComponents collection is a sub object (i.e. VB component collection) under the VBProject object, including all worksheets, forms, modules, etc. (i.e. objects visible in the Project Explorer of VBE). Use the Add method of this collection to Add VB components. Its syntax is
VBComponents.Add(component)
This method can return a VB component object. The parameter component is the component to be added, and can be any constant in the table.
| often Set | say bright |
| Vbext_ct_ClassModule | Add class module |
| vbext_ct_MSForm | Add Form |
| Vbext_ct_StdModule | Add standard module |
After adding a form using the Add method of VBComponents, its form will exist in the VB project of the current workbook. When you need to delete VBComponent objects, you can use the Remove method of VBComponents. Its syntax is as follows:
VBComponents.Remove(component)
The parameter component is the component object to be deleted.
When you need to operate VBComponents, you must check "trust access to VBA project object model" in the "macro setting" option of Excel (see operation step 3 of this example).
VBComponent object
When a form is added using the Add method of VBComponents, the VBComponent object is returned. Although form object is a part of VBA project, its object model is different from VBComponent object. Therefore, the VBComponent object cannot directly access the properties of the form object or execute its methods.
When you need to read or modify the Properties of a VBComponent object, you need to access it through its Properties collection. Its syntax is
VBComponent.Properties(propname)[=value]
Where propname is the name of the attribute to be accessed, and value is the value of the attribute.
The Designer model of VB component can be accessed through the Designer property of VBComponent. Under the Designer, you can use the Add method of Controls to Add Controls in the form, which is the same as adding Controls in the form.
When you need to display the added form, you need to add the form to the Userforms collection of VBA. Its syntax is
VBA.UserForms.Add(componentname).Show
Where componentname is the name of the added form.
After the form is displayed, all subsequent codes will be executed after the form is closed, so the property settings of the form and the addition of form controls must be placed before the form display code.