[µo°Ý] Àx¦s®æ¤º®eÅܧó«á¦Û°Ê²¾¨ì¤U¤@®æ

owen06

½Ð±Ð¦U¦ì¤j¤j¡A§Ú³o­ÓÀɮצb«ö¤U¡yMADE¡z«á¡A·|²£¥Í¤@­Ó½d³ò¡A½d³ò¦WºÙ¥s¡ymysize¡z
¥k¤âÃ䦳¤@°ï«ö¶s¡A«ö¤U¥h«á¡Aactivecell·|µ¥©ó«ö¶sªº¦W¦r¡A
½Ð°Ý¸Ó¦p¦ó³]©w¡A¤~¯àÅý¥L¦b«ö¤U¥kÃä«ö¶s«á¡Amysizeªº²Ä¤@®æ·|²£¥Í¸ê®Æ¡AµM«áÀx¦s®æ·|¨Ì·Ó¶¶§Ç©¹¤U¤@®æ²¾°Ê¡A
¨Ò¡G¥»¨Ó¦bmysize.cells(1),«ö¤U«ö¶s¿é¤J¸ê®Æ«á¡AºX¼Ð´N¸õ¨ìmysize.cells(2)¡A¦A«ö¤@¦¸«ö¶s¡AºX¼Ð´N¸õ¨ìmysize.cells(3)

·Ð½Ð¤j¤j¤£§[«ü±Ð¡AÁÂÁÂ~~~

yen956

¦^´_ 1# owen06
¸Õ¸Õ¬Ý:
    ActiveCell.Offset(1, 0).Select           '¬¡°Ê³æ¤¸®æ¤U²¾¤@®æ
    ActiveCell.Offset(-1, 0).Select          '¬¡°Ê³æ¤¸®æ¤W²¾¤@®æ
    ActiveCell.Offset(0, 1).Select           '¬¡°Ê³æ¤¸®æ¥k²¾¤@®æ
    ActiveCell.Offset(0, -1).Select          '¬¡°Ê³æ¤¸®æ¥ª²¾¤@®æ

GBKEE

¦^´_ 1# owen06
¨Ï¥Îª«¥óÃþ§O

1. 'ThisWorkbook¼Ò²Õ
   
2. Dim Ar() As New Class1 'ª«¥óÃþ§O¼Ò²Õ [¦WºÙ: Class1]
   
3. Private Sub Workbook_Open()  
   
4.     Dim E As OLEObject, i As Integer
   
5.     With Sheet1
   
6.         For Each E In .OLEObjects
   
7.             If E.progID = "Forms.CommandButton.1" Then
   
8.                 If UCase(E.Object.Caption) <> "CLEAR" And UCase(E.Object.Caption) <> "MADE" Then
   
9.                     ReDim Preserve Ar(0 To i)
   
10.                     Set Ar(i).Class_CommandButton = E.Object
    
11.                     i = i + 1
    
12.                 End If
    
13.             End If
    
14.         Next
    
15.        Run "Sheet1.CommandButton2_Click"
    
16.     End With
    
17. End Sub

½Æ»s¥N½X

1. Public mysize As Range    'Sheet1¼Ò²Õ
   
2. Private Sub CommandButton1_Click()
   
3. k1 = [I2].Value
   
4. k2 = [i3].Value
   
5. Set mysize = [B2].Resize(k1, k2)
   
6. mysize.clear
   
7. End Sub
   
8. Private Sub CommandButton2_Click()
   
9. 'Dim mysize As Range
   
10. k1 = [I2].Value
    
11. k2 = [i3].Value
    
12. Set mysize = [B2].Resize(k1, k2)
    
13. With mysize
    
14. .Borders.LineStyle = xlContinuous
    
15. .Interior.ColorIndex = 37
    
16. .VerticalAlignment = xlCenter
    
17. .HorizontalAlignment = xlCenter
    
18. End With
    
19. mysize.Cells(1).Select
    
20. End Sub

½Æ»s¥N½X

1. Option Explicit  '·s¼Wª«¥óÃþ§O¼Ò²Õ [¦Û°Ê©R¦W: Class1]
   
2. Public WithEvents Class_CommandButton As MSForms.CommandButton
   
3. Private Sub Class_CommandButton_Click()
   
4.     Dim i As Integer
   
5.     With Class_CommandButton.Parent.mysize
   
6.         .Cells(Application.CountA(.Cells) + 1) = Class_CommandButton.Caption
   
7.     End With
   
8. End Sub

½Æ»s¥N½X

owen06

¦^´_ 3# GBKEE


    ÁÂÁª©¥Dªº¦^ÂСA§Ú·Ó§Aªº¤èªk³]©w§¹«á¡A«ö¤U¥kÃä¥ô¤@«ö¶s¡A¥X²{
    °õ¦æ¶¥¬q¿ù»~¡G438¡Cª«¥ó¤£¤ä´©¦¹ÄݩʩΤèªk¡C
    «ö¤U°»¿ù¼Ð¥Üªº¤º®e¬°¡GWith Class_CommandButton.Parent.mysize
    ³Â·Ð§A©âªÅ¦AÀ°§Ú¬Ý¤@¤U¬O­þÃä³]©w¤£¹ï¡AÁÂÁ§A¤F~

owen06

¦^´_ 3# GBKEE


    ®@¡Aª©¥D§A¦n¡A¬Ý¤F§Aªº¤½¦¡¤§«á¡A§Ú·Q¤F¥t¤@­Ó¤èªk¡A¿é¤J¦bsheet1¸Ì­±´N¦¨¥\¤F¡AÁÂÁ§Aªº±Òµo

1. Private Sub Worksheet_Change(ByVal Target As Range)
   
2. Dim mysize As Range
   
3. k1 = [I2].Value
   
4. k2 = [i3].Value
   
5. Set mysize = [B2].Resize(k1, k2)
   
6. If mysize.Cells(1) <> "" Then mysize.Cells(Application.CountA(mysize) + 1).Select
   
7. End Sub

½Æ»s¥N½X

GBKEE

¦^´_ 4# owen06

1. Public mysize As Range   '¼Ò²Õ³»ºÝ¨î©w¬°¦¹¼Ò²Õªº¤½¥ÎÅܼÆ,¨ä¥L¼Ò²Õªºµ{§Ç¥i¨Ï¥Î¦¹ÅܼÆ
   
2. 'Dim mysize As Range   '¼Ò²Õ³»ºÝ¨î©w¬°¦¹¼Ò²Õªº¨p¥ÎÅܼÆ,¶È¦¹¼Ò²Õªºµ{§Ç¥i¨Ï¥Î¦¹ÅܼÆ,¨ä¥L¼Ò²Õªºµ{§Ç¤£¥i¨Ï¥Î¦¹ÅܼÆ
   
3. Private Sub CommandButton1_Click()
   
4. 'Dim mysize As Range  '¦¹µ{§Çªº¨p¥ÎÅܼÆ,¶È¦¹µ{§Ç¤¤¥i¥Î  
   
5. k1 = [I2].Value
   
6. k2 = [i3].Value
   
7. Set mysize = [B2].Resize(k1, k2)
   
8. mysize.clear
   
9. End Sub
   
10. Private Sub CommandButton2_Click()
    
11. 'Dim mysize As Range
    
12. k1 = [I2].Value
    
13. k2 = [i3].Value
    
14. Set mysize = [B2].Resize(k1, k2)
    
15. With mysize
    
16. .Borders.LineStyle = xlContinuous
    
17. .Interior.ColorIndex = 37
    
18. .VerticalAlignment = xlCenter
    
19. .HorizontalAlignment = xlCenter
    
20. End With
    
21. mysize.Cells(1).Select
    
22. End Sub
    
23. '«á­±ªºCommandButton??_Click ¤£¦b»Ý­n¤F
    
24. Private Sub CommandButton10_Click()
    
25. ActiveCell = CommandButton10.Caption
    
26. End Sub

½Æ»s¥N½X