Hsieh

¦^´_ 1# caichen3


¤U©Ô²M³æ¤º®e¬O¨º¨Ç?
§@¥Î®É¾÷?°ÝÃD½Ð´y­z²M·¡
¥ý¥H§ó§ïSheet3!A1:A2¼Æ­È¬°¨Ò¡A¦Û¤v¨Ì»Ý¨D§ó§ï

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim Ar()
   
3. If Intersect(Target, [A1:A2]) Is Nothing Then Exit Sub
   
4. Application.ScreenUpdating = False
   
5. r = [A1]  '¦C¼Æ
   
6. K = [A2]  'Äæ¼Æ
   
7.     For Each C In Sheet1.[B2].Resize(r, 1)  '¼W¥[²M³æ¤º®e(«È¤á¯u¥¿»Ý¨D)
   
8.       ReDim Preserve Ar(s)
   
9.       Ar(s) = C.Value
   
10.       s = s + 1
    
11.     Next
    
12.     For Each C In Sheet2.[E2].Resize(, K) '¼W¥[²M³æ¤º®e(«~½è¯S©Ê)
    
13.       ReDim Preserve Ar(s)
    
14.       Ar(s) = C.Value
    
15.       s = s + 1
    
16.     Next
    
17. 
    
18. Me.OLEObjects.Delete
    
19. For i = 1 To r
    
20.     For j = 1 To K
    
21.     Set a = Cells(i + 1, j + 4)
    
22.     With Me.OLEObjects.Add(ClassType:="Forms.ComboBox.1")  '´¡¤Jª«¥ó
    
23.     .Top = a.Top
    
24.     .Left = a.Left
    
25.     .Height = a.Height
    
26.     .Width = a.Width
    
27.     .LinkedCell = a.Address
    
28.     .Object.List = Ar
    
29.     End With
    
30.     Next
    
31. Next
    
32. Application.ScreenUpdating = True
    
33. End Sub

½Æ»s¥N½X