[發問] 動態新增 UserForm 及 CommandButton 後 如何寫click的動作？

C.F

我以下列程式碼 新增一個 UserForm 、CommandButton 再用一個 Class 來執行 Button Click 的動作，但 似乎 一直無法 照著 Class 內的動作去執行 ~~
我不知是哪個部份出了問題 ~~

   
   Dim MyNewForm As Object
    Dim NewListBox1 As MSForms.ListBox
    Dim NewLabel1 As MSForms.Label
    Dim NewCommandButton1 As MSForms.CommandButton
   
   
    Dim Btn1 As New Class1
    Dim MyAddItemCode$
    Dim UF_Name$
   
'   Create the UserForm
    Set MyNewForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
   
    With MyNewForm
         .Properties("Caption") = "Test_Form1"
         .Properties("Width") = 540
         .Properties("Height") = 350
         '.Name = "Test_Form1"
    End With


      Set NewCommandButton1 = MyNewForm.Designer.Controls _
      .Add("forms.CommandButton.1")
      
    With NewCommandButton1
   
        .Name = "Add_ItemBtn"
        .Left = 235
        .Top = 36
        .Height = 22
        .Width = 50
        .Caption = "新增"
        .Font.Name = "標楷體"
        .Font.Size = 12
        
    End With
        
  
    Set Btn1.CmdBtn = NewCommandButton1

      '   Show the form
    VBA.UserForms.Add(UF_Name$).Show 'vbModeless

    'Delete the form
    ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=MyNewForm

    'ThisWorkbook.VBProject.VBComponents.Remove VBComponent:=Test1
    Set MyNewForm = Nothing


Class 的內容


Option Explicit

Public WithEvents CmdBtn As MSForms.CommandButton
Private Sub CmdBtn_Click()
                MsgBox "Test O.K ! "
End Sub

mark15jill

回復 1# C.F


    你要的目的是什麼?
     空白表單 產出 CommandButton?
     然後 點CommandButton 要有什麼動作?

C.F

其實我還有兩個 ListBox，我主要是點了Button 後 要將左邊 ListBox 的文字 帶到右邊去 ~~
上方是我先測試一下，結果就不成功了 ~~

mark15jill

回復 3# C.F


點下去 左邊到右邊 是複製還是單純搬移?

C.F

是要搬移的

Hsieh

1. Sub Auto_Open()
   
2. Dim MyForm As VBComponent
   
3. Set MyForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
   
4. With MyForm
   
5.     .Properties("Caption") = "Test_Form"
   
6.     .Name = "Test_Form1"
   
7.     With .Designer
   
8.       With .Controls.Add("Forms.CommandButton.1")
   
9.           .Top = 50
   
10.           .Left = 100
    
11.           .Height = 20
    
12.           .Width = 20
    
13.           .Caption = ">>"
    
14.           .Name = "Move_Data"
    
15.       End With
    
16.       For i = 1 To 2
    
17.       With .Controls.Add("Forms.Listbox.1")
    
18.           .Top = 10
    
19.           .Left = (i - 1) * 100 + 20
    
20.           .Height = 120
    
21.           .Width = 80
    
22.           .Name = "MyList" & i
    
23.       End With
    
24.       Next
    
25.     End With
    
26.     With .CodeModule
    
27.       .InsertLines 1, "Private Sub UserForm_Initialize()"
    
28.       .InsertLines 2, "MyList1.List=array(1,2,3,4,5,6,7,8,9)"
    
29.       .InsertLines 3, "End Sub"
    
30.       .InsertLines 4, "Private Sub Move_Data_Click()"
    
31.       .InsertLines 5, "x = MyList1.ListIndex"
    
32.       .InsertLines 6, "MyList2.AddItem  MyList1.List(x)"
    
33.       .InsertLines 7, "MyList1.RemoveItem x"
    
34.       .InsertLines 8, "End Sub"
    
35.     End With
    
36. End With
    
37. With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
    
38.   .InsertLines 1, "Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(10) & _
    
39. "With ThisWorkbook" & Chr(10) & _
    
40. ".VBProject.VBComponents.Remove .VBProject.VBComponents(""Test_Form1"")" & Chr(10) & _
    
41. "n = .VBProject.VBComponents(""ThisWorkbook"").CodeModule.CountOfLines" & Chr(10) & _
    
42. ".VBProject.VBComponents(""ThisWorkbook"").CodeModule.DeleteLines 1, n" & Chr(10) & _
    
43. ".Save" & Chr(10) & _
    
44. "End With" & Chr(10) & _
    
45. "End Sub"
    
46. End With
    
47. 'OpenForm '開啟檔案自動開啟表單
    
48. End Sub
    
49. Sub OpenForm()
    
50. 'Test_Form1.Show 0 '開啟表單
    
51. End Sub

複製代碼

回復 5# C.F


    是這樣的效果嗎?

下載 (90.91 KB)

2013-1-23 10:23

動態新增表單.zip (13.91 KB)

下載次數: 20

2013-1-23 10:30

一般模組

C.F

感謝您 ~~ 這的確是我要的~~~
只是因為我還會加其它功能 ~~ 所以我相將 按鈕的程式碼部份放到 class 中
請問可以怎麼做？

Hsieh

回復 7# C.F
class1模組

1. Public WithEvents MyBut As MSForms.CommandButton
   
2. 
   
3. Private Sub MyBut_Click()
   
4. With Test_Form1
   
5.   x = .MyList1.ListIndex
   
6.   If x = -1 Then Exit Sub
   
7.   .MyList2.AddItem .MyList1.List(x)
   
8.   .MyList1.RemoveItem x
   
9. End With
   
10. End Sub

複製代碼

Module1模組

1. Public obj As New Class1
   
2. Sub Auto_Open()
   
3. Dim MyForm As VBComponent
   
4. Set MyForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
   
5. With MyForm
   
6.     .Properties("Caption") = "Test_Form"
   
7.     .Name = "Test_Form1"
   
8.     With .Designer
   
9.       With .Controls.Add("Forms.CommandButton.1")
   
10.           .Top = 50
    
11.           .Left = 100
    
12.           .Height = 20
    
13.           .Width = 20
    
14.           .Caption = ">>"
    
15.           .Name = "Move_Data"
    
16.       End With
    
17.       For i = 1 To 2
    
18.       With .Controls.Add("Forms.Listbox.1")
    
19.           .Top = 10
    
20.           .Left = (i - 1) * 100 + 20
    
21.           .Height = 120
    
22.           .Width = 80
    
23.           .Name = "MyList" & i
    
24.       End With
    
25.       Next
    
26.     End With
    
27.     With .CodeModule
    
28.       .InsertLines 1, "Private Sub UserForm_Initialize()"
    
29.       .InsertLines 2, "MyList1.List=array(1,2,3,4,5,6,7,8,9)"
    
30.       .InsertLines 3, "Set obj.MyBut = Controls(""Move_Data"")"
    
31.       .InsertLines 4, "End Sub"
    
32.     End With
    
33. End With
    
34. With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
    
35.   .InsertLines 1, "Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(10) & _
    
36. "With ThisWorkbook" & Chr(10) & _
    
37. ".VBProject.VBComponents.Remove .VBProject.VBComponents(""Test_Form1"")" & Chr(10) & _
    
38. "n = .VBProject.VBComponents(""ThisWorkbook"").CodeModule.CountOfLines" & Chr(10) & _
    
39. ".VBProject.VBComponents(""ThisWorkbook"").CodeModule.DeleteLines 1, n" & Chr(10) & _
    
40. ".Save" & Chr(10) & _
    
41. "End With" & Chr(10) & _
    
42. "End Sub"
    
43. End With
    
44. 'OpenForm '開啟檔案自動開啟表單
    
45. End Sub
    
46. Sub OpenForm()
    
47. 'Test_Form1.Show 0 '開啟表單
    
48. End Sub

複製代碼

動態新增表單.rar (16.51 KB)

下載次數: 15

2013-1-23 21:52

C.F

非常感謝您的指導 ~~ 但我遇到一個問題，我之前就遇到 了 ~~

執行這行的時候有時會無法執行 ~~ 不知為何 無法會如 此~
.Name = "Test_Form1"

C.F

會出現 error 75 訊息 ~~