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

½Æ»s¥N½X

¦^´_ 5# C.F


    ¬O³o¼Ëªº®ÄªG¶Ü?

¤U¸ü (90.91 KB)

2013-1-23 10:23

°ÊºA·s¼Wªí³æ.zip (13.91 KB)

¤U¸ü¦¸¼Æ: 20

2013-1-23 10:30

¤@¯ë¼Ò²Õ

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

½Æ»s¥N½X

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

½Æ»s¥N½X

°ÊºA·s¼Wªí³æ.rar (16.51 KB)

¤U¸ü¦¸¼Æ: 15

2013-1-23 21:52

Hsieh

1. Public obj As New Class1
   
2. Sub Auto_Open()
   
3. Dim MyForm As VBComponent
   
4. For Each vbc In ThisWorkbook.VBProject.VBComponents
   
5.    If vbc.Type = 3 Then 'Àˬdªí³æ¼Ò²Õ
   
6.       If vbc.Name = "Test_Form1" Then Exit Sub '¦³¸Óªí³æ«h¸õ¥Xµ{§Ç
   
7.    End If
   
8. Next
   
9. Set MyForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm) '·s¼Wªí³æ¼Ò²Õ
   
10. With MyForm
    
11.     .Properties("Caption") = "Test_Form" 'ªí³æ¼ÐÃD
    
12.     .Name = "Test_Form1" 'ªí³æ¦WºÙ
    
13.     With .Designer
    
14.       With .Controls.Add("Forms.CommandButton.1") '·s¼W«ö¶s
    
15.           .Top = 50
    
16.           .Left = 100
    
17.           .Height = 20
    
18.           .Width = 20
    
19.           .Caption = ">>"
    
20.           .Name = "Move_Data"
    
21.       End With
    
22.       For i = 1 To 2
    
23.       With .Controls.Add("Forms.Listbox.1") '²M³æ
    
24.           .Top = 10
    
25.           .Left = (i - 1) * 100 + 20
    
26.           .Height = 120
    
27.           .Width = 80
    
28.           .Name = "MyList" & i
    
29.       End With
    
30.       Next
    
31.     End With
    
32.     With .CodeModule '¦bªí³æ¼Ò²Õ¤º·s¼Wµ{§Ç
    
33.       .InsertLines 1, "Private Sub UserForm_Initialize()"
    
34.       .InsertLines 2, "MyList1.List=array(1,2,3,4,5,6,7,8,9)"
    
35.       .InsertLines 3, "Set obj.MyBut = Controls(""Move_Data"")" '±N«ö¶s¥[¤Jª«¥óÃþ§O
    
36.       .InsertLines 4, "End Sub"
    
37.     End With
    
38. End With
    
39. With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule '¼g¤JÃö³¬ÀÉ®×µ{§Çªºµ{¦¡½X
    
40.   .InsertLines 1, "Private Sub Workbook_BeforeClose(Cancel As Boolean)" & Chr(10) & _
    
41. "With ThisWorkbook" & Chr(10) & _
    
42. ".VBProject.VBComponents.Remove .VBProject.VBComponents(""Test_Form1"")" & Chr(10) & _
    
43. "n = .VBProject.VBComponents(""ThisWorkbook"").CodeModule.CountOfLines" & Chr(10) & _
    
44. ".VBProject.VBComponents(""ThisWorkbook"").CodeModule.DeleteLines 1, n" & Chr(10) & _
    
45. ".Save" & Chr(10) & _
    
46. "End With" & Chr(10) & _
    
47. "End Sub"
    
48. End With
    
49. 'OpenForm '¶}±ÒÀɮצ۰ʶ}±Òªí³æ
    
50. End Sub
    
51. Sub OpenForm()
    
52. 'Test_Form1.Show 0 '¶}±Òªí³æ
    
53. End Sub

½Æ»s¥N½X

¦^´_ 10# C.F


    À³¸Ó¬O§Aªºªí³æ¤w¸g¦s¦b¡A¦Ó§A¦b¤@¦¸°õ¦æ¸Óµ{§Ç¡A²£¥Í¦WºÙ½Ä¬ð
¸Õ¸Õ¬Ý

Hsieh

¦^´_ 12# C.F


   ¤â°Ê²¾°£ªí³æ«á¡A¦]°O¾ÐÅ餴¥¼ÄÀ©ñ¦¹ªí³æ
½Ð³s¦PThisworkbook¼Ò²Õ¤ºªº©Ò¦³µ{¦¡½X§R°£«á¦sÀÉ
§Y¥i°õ¦æAuto_Openµ{§Ç

Hsieh

¦^´_ 17# wanmas
¦]¬°¦¹°ÝÃD¬O±q·s¼Wªí³æ¶}©l
¦¹¬qµ{§Ç¬Oªí³æ¼Ò²Õªºªì©l¤Æµ{§Ç
¥Øªº¦b·íªí³æ¶}±Ò®É±Nªí³æ¤ºª«¥óªº³]©w¥[¤J
­Ó¤H¨Ã¤£«Øij¨Ï¥Î³o¼Ëªº¤èªk¾Þ§@ªí³æ
VBA¨Æª«¥ó¾É¦Vªºµ{¦¡»y¨¥
°£«D¯S§O¥Øªº¡A§_«hÀ³¥ý±Nª«¥ó³£³]¸m¦n
¨Ã¥B¨C­Óª«¥ó©Ò»Ýªºµ{§Ç¹w¥ý¼g¦n
©¡®É¥u»Ý±Ò°Êªí³æ§Y¥i°õ¦æ
³o¼Ë¦bµ{¦¡¼¶¼g¤¤¤ñ¸û®e©ö°»¿ù»P­×¥¿