c_c_lai

¦^´_  c_c_lai

ÁÙ¬O¤£¦æ¡A«öA2¡BA3...³£¨S¦³¤ÏÀ³
¤§«e¼Ó¥DªºÀɮפ]¤£¦æ¡A«á¨Ó§Ú¦Û¦æ³]©w ¸ê®ÆÅçÃÒ(²M³æ ...
ML089 µoªí©ó 2013-11-6 12:47

½Ð°Ý§A¬O¤U¸ü "¬Û¤ù¿é¥X»ù¥Øªí.xlsm" ª½±µ¥Î¥¦¨Ó´ú¸Õ¡AÁÙ¬O
¦Û¤v¥t¥~¶}±Ò¤@·sÀɮסA¦Ó¶È½Æ»sµ{¦¡½X¨Ó¹B§@¡H

ML089

¦^´_ 14# c_c_lai

ÁÙ¬O¤£¦æ¡A«öA2¡BA3...³£¨S¦³¤ÏÀ³
¤§«e¼Ó¥DªºÀɮפ]¤£¦æ¡A«á¨Ó§Ú¦Û¦æ³]©w ¸ê®ÆÅçÃÒ(²M³æ) ¡A¤U©Ô¿ï³æ¤~¥X²{

½Ð°Ý ComboBox1 ¬O§_»Ý­n¦Û¦æ¼W³]¡AÁÙ¬Oµ{¦¡¤w¸g¤º©w´N¦³

c_c_lai

¦^´_ 12# ML089
§A¦A¸Õ¸Õ¬Ý¡A´ú¸Õµ²ªG¦p¦ó¦A¦æ§i¶D§Ú¡C
ÁÂÁ¡I

c_c_lai

¦^´_  c_c_lai

§ÚªºEXCEL¬O2007
¶}±Ò§AªºÀɮסA¨ä¤u§@ªí¦WºÙ¬O Sheet1¡B Sheet2¡B Sheet3
¦ýVBAµ{¦¡¤¤ ...
ML089 µoªí©ó 2013-11-6 10:49

¤§«e§Ú¬O¥t¥~·s¼W¤@ExcelÀÉ®×´ú¸Õ¡A§¹¦¨«á«Kª½±µ±Nµ{¦¡½X¶K¤J¨ì­ì¥»ªº
"¬Û¤ù¿é¥X»ù¥Øªí.xlsm" ¤º¡A§Ñ¤F±N¨ä¹ïÀ³ªºªí³æ¦WºÙ¤@¨Ö­×¥¿¡A¯u¬O¹ï¤£°_¡I
¬Û¤ù¿é¥X»ù¥Øªí.rar (24.83 KB)

¤U¸ü¦¸¼Æ: 8

2013-11-6 11:51

ML089

¦^´_ 10# c_c_lai

§ÚªºEXCEL¬O2007
¶}±Ò§AªºÀɮסA¨ä¤u§@ªí¦WºÙ¬O Sheet1¡B Sheet2¡B Sheet3
¦ýVBAµ{¦¡¤¤¥Îªº¤u§@ªí¦WºÙ¬O ¤u§@ªí1¡B¤u§@ªí2
§Ú¸ÕµÛ±N VBA¤¤ªº "¤u§@ªí" §ï¬° "Sheet" ¤]¤£¦æ
¸Ó¦p¦ó¬O¦n  

¨Ò¦p
Sub CellValidation()      '  stillfish00 ´£¨Ñ
    With Sheets("¤u§@ªí2").[A2:A25].Validation
        .Delete
        .Add Type:=xlValidateList, Formula1:="=¤u§@ªí1!$A$3:$A$20"
    End With
End Sub

ML089

¦^´_ 10# c_c_lai

ÁÂÁ§A´£¨Ñ¨º»ò¸Ô²Óªº»¡©ú

§ÚªºEXCEL¶}±Ò(¦w¥þ¦P·N¤]¶}±Ò)¤]¨Ì·Óµe­±¨BÆJ¥hÂI¿ïA2¡A¥i¬O¨S¦³¥X²{¤U©Ôªí¡A¬O§_§Ú¯Ê¤Ö¤°»òª«¥ó

¤U¸ü (49.79 KB)

2013-11-6 10:36

c_c_lai

¦^´_  c_c_lai

§Ú¤U¸ü§AªºÀɮסAÂI¿ïSheet2 - A2¨S¦³¥X²{¤U©Ô¿ï³æ¡A
½Ð°Ý§ÚÁٻݭn°µ­þ¨Ç³]©w¡C
ML089 µoªí©ó 2013-11-6 09:14

¤U¸ü (101.99 KB)

2013-11-6 10:13

ML089

¦^´_ 7# c_c_lai

§Ú¤U¸ü§AªºÀɮסAÂI¿ïSheet2 - A2¨S¦³¥X²{¤U©Ô¿ï³æ¡A
½Ð°Ý§ÚÁٻݭn°µ­þ¨Ç³]©w¡C

av8d

ÁÂÁÂ~stillfish00 ¤j¤jªº¸Ñµª
Åýc_c_lai¤j¤j¸Ñµª¤F§Ú¯u¥¿·Q­n¨Ï¥Îªº¥\¯à~

¨â¦ì¤j¤jÁÂÁÂ~¾Ç²ß¤F

c_c_lai

¦^´_ 2# av8d
ºî¦X¤F stillfish00 ¤j¤j´£¨Ñªº¨ç¼Æ¡A¥H¤Î¥[¤W¾ã¦X§Aªº»Ý¨D¦Ó¦¨¡C
1.   " ÂI¤@¤UA2~·|¥X²{¤U©Ô¿ï³æ¡A¿ï¨ú«á~¦p¦ó¦Û°Ê¸õ¨ìC2 "¡A
2.   " ·í¼Æ¶q¿é¤J«á«ö¤UEnter«á~¦Û°Ê¸õ¨ì³W®æ¦WºÙ (C2¿é¤J¼Æ¦r«á«ö¤UEnter«á~¦Û°Ê¸õ¨ìA2) "
²Ä¤G¶µ»Ý¨D¡A­×§ï¦¨¡G
·í¼Æ¶q¿é¤J«á«ö¤UEnter«á~¦Û°Ê¸õ¨ì¤U¤@­Ó³W®æ¦WºÙ (C2¿é¤J¼Æ¦r«á«ö¤UEnter«á~¦Û°Ê¸õ¨ìA3 )

1. Public ckCurr As Boolean
   
2. 
   
3. Private Sub ComboBox1_Change()    '  stillfish00 ´£¨Ñ
   
4.     If ckCurr Then Exit Sub
   
5.    
   
6.     Application.EnableEvents = False
   
7.    
   
8.     ckCurr = False
   
9.     ComboBox1.Visible = False
   
10.     Range(ComboBox1.LinkedCell).Offset(, 2).Select
    
11.     Application.EnableEvents = True
    
12. End Sub
    
13. 
    
14. Private Sub CommandButton1_Click()
    
15.     If Me.ComboBox1.Visible Then ckCurr = True: Me.ComboBox1.Visible = False
    
16.    
    
17.     Range("A2:A25,C2:C25").Select
    
18.     Selection.ClearContents
    
19. End Sub
    
20. 
    
21. Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
22.     Dim StrVdFml As String
    
23.    
    
24.     '  If ckCurr Then Exit Sub
    
25.     On Error Resume Next
    
26.         StrVdFml = Replace(ActiveCell.Validation.Formula1, "=", "")
    
27.         '  ActiveCell.Validation.Formula1 ¡G  "=¤u§@ªí1!$A$3:$A$20"
    
28.         '  ComboBox1.ListFillRange        ¡G   ¤u§@ªí1!$A$3:$A$20
    
29.         '  Replace(ActiveCell.Validation.Formula1, "=", "") : "¤u§@ªí1!$A$3:$A$20"
    
30.         '  StrVdFml : "¤u§@ªí1!$A$3:$A$20"
    
31.         ActiveCell.Validation.InCellDropdown = False
    
32.     On Error GoTo 0
    
33.     If StrVdFml = "" Then
    
34.         If Me.ComboBox1.Visible Then Me.ComboBox1.Visible = False
    
35.     Else
    
36.         With Me.ComboBox1
    
37.             '  ComboBox1.progID   =EMBED("Forms.ComboBox.1","")
    
38.             '  ComboBox1¡G        ComboBox
    
39.             '  LinkedCell¡G       $A$2
    
40.             '  ListFillRange ¡G   ¤u§@ªí1!$A$3:$A$20
    
41.             .Left = ActiveCell.Left
    
42.             .Top = ActiveCell.Top
    
43.             '  .Width = ActiveCell.Width + 140
    
44.             .Width = ActiveCell.Width
    
45.             '  .Height = ActiveCell.Height + 10
    
46.             .Height = ActiveCell.Height
    
47.             '  .Font.Size = 22
    
48.             .Font.Size = 12
    
49. 
    
50.             .LinkedCell = ActiveCell.Address    '  "$A$2"
    
51.             .ListFillRange = StrVdFml           '  "¤u§@ªí1!$A$3:$A$20"
    
52.             .Visible = 1                        '  Åã¥Ü¤U©Ô²Å¸¹
    
53. 
    
54.             .Object.SpecialEffect = 3
    
55.             '.Object.Font.Size = ActiveCell.Font.Size
    
56.         End With
    
57.     End If
    
58.   
    
59.     ckCurr = False
    
60. End Sub
    
61. 
    
62. Private Sub Worksheet_Change(ByVal Target As Range)
    
63.     '  Target.Font.ColorIndex = 5
    
64.    
    
65.     If Not Intersect(Target, Range("C2:C25")) Is Nothing Then
    
66.         If Target(1, 1) = 0 Then Exit Sub
    
67.         ckCurr = True
    
68.         '  MsgBox Target.Address
    
69.         Target.Offset(1, -2).Select
    
70.     End If
    
71. End Sub
    
72. 
    
73. Sub CellValidation()      '  stillfish00 ´£¨Ñ
    
74.     With Sheets("¤u§@ªí2").[A2:A25].Validation
    
75.         .Delete
    
76.         .Add Type:=xlValidateList, Formula1:="=¤u§@ªí1!$A$3:$A$20"
    
77.     End With
    
78. End Sub

½Æ»s¥N½X