Excel--使用VBA Code 動態建立、修改和刪除自定義窗體程式碼摘抄

Sub CreateUserform()

'PURPOSE: Create & Modify a Userform with VBA Code
'AUTHOR: John Walkenbach (www.SpreadsheetPage.com)
'SOURCE: www.TheSpreadsheetGuru.com

'******************************************************************
'NOTE: You need to set a reference to the extensibility add-in.
'To do this, go to Tools -> References and find the add-in
'Microsoft Visual Basic for Applications Extensibility 5.3
'******************************************************************

Dim myUserform As Object
Dim FormName As String
Dim NewButton As MSForms.CommandButton
Dim TextLocation As Integer
Dim x As Integer

    'Locks Excel Spreadsheet and Speeds Up Form Processing
        Application.VBE.MainWindow.Visible = False
        Application.ScreenUpdating = False
    
    'Create a New UserForm
        Set myUserform = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    
    'Set Properties for Userform (aka: myUserform)
        With myUserform
            .Properties("Caption") = "Temporary Form"
            .Properties("Width") = 200
            .Properties("Height") = 100
        End With
    
    FormName = myUserform.Name

    'Add a CommandButton and Modify it's Properties
        Set NewButton = myUserform.Designer.Controls.Add("forms.CommandButton.1")
            With NewButton
                .Caption = "Click Me"
                .Left = 60
                .Top = 40
            End With

    'Add an event-hander Sub for your CommandButton
        With myUserform.CodeModule
            x = .CountOfLines
            .InsertLines x + 1, "Sub CommandButton1_Click()"
            .InsertLines x + 2, "MsgBox ""Hello!"""
            .InsertLines x + 3, "Unload Me"
            .InsertLines x + 4, "End Sub"
        End With

    'Show This Form
        VBA.UserForms.Add(FormName).Show

    'Delete This Form
        ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=myUserform

End Sub