- ©«¤l
- 5923
- ¥DÃD
- 13
- ºëµØ
- 1
- ¿n¤À
- 5986
- ÂI¦W
- 0
- §@·~¨t²Î
- win10
- ³nÅ骩¥»
- Office 2010
- ¾\ŪÅv
- 150
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW°ò¶©
- µù¥U®É¶¡
- 2010-5-1
- ³Ì«áµn¿ý
- 2022-1-23
|
¥»©«³Ì«á¥Ñ GBKEE ©ó 2012-10-13 15:33 ½s¿è
¦^´_ 1# li918272002
¥ý¦³¬ö¿ý,¤~¥i¬d¬Ý
·s¶}Àɮפ¤, ½Æ»s¤U¦C¤TÓ¼Ò²Õ, ¦s¬°¼W¯q¶°ÀÉ®×, ¸ü¤J¦¹¼W¯q¶°ÀÉ®×
º¸«áExcel¤¤¶}±Ò¤w¦sÀɪºÀÉ®× ¥i¦s¤Uקï°O¿ý
test.rar (26.17 KB)
´¡¤J ªí³æ(¦WºÙ UserForm1 )ªºµ{¦¡½X ¶·¦³±±¨î¶µ ComboBox1,ListBox1,ListBox2- '´¡¤J ¤@ªí³æ(¦WºÙ UserForm1 )ªºµ{¦¡½X ¶·¦³±±¨î¶µ ComboBox1,ListBox1,ListBox2
- Option Explicit
- Dim xlFile As String
- Private Sub ComboBox1_Change()
- If ComboBox1.ListIndex > -1 Then
- Ū¨ú°lÂÜÀÉ
- Else
- ListBox1.Clear
- End If
- End Sub
- Private Sub UserForm_Activate()
- xlFile = ActiveWorkbook.Path & °lÂÜÀÉ
- Caption = ActiveWorkbook.Path & °lÂÜÀÉ
- If Dir(xlFile) = "" Then
- Me.Hide
- MsgBox ActiveWorkbook.Path & " ¨S¦³Åܧ󪺸ê®Æ °lÂÜÀÉ "
- Unload Me
- Else
- ªí³æ³]©w
- End If
- End Sub
- Private Sub ªí³æ³]©w()
- Dim E As String
- Top = 66.95
- Left = 165.75
- Height = 343.85
- Width = 552.5
- With ListBox1
- .Top = 58.9
- .Left = 17.65
- .Height = 247.5
- .Width = 507.45
- .ColumnCount = 4
- .ColumnWidths = "90,130,130,130"
- .Font.Size = 12
- End With
- With ListBox2
- .Top = 35.35
- .Left = 17.65
- .Height = 21.95
- .Width = 507.5
- .ColumnCount = 4
- .ColumnWidths = "90,130,130,130"
- .TextAlign = fmTextAlignCenter
- .Font.Size = 16
- End With
- With ComboBox1
- .Top = 11.8
- .Left = 17.65
- .Height = 15.05
- .Width = 94.3
- E = Dir(ActiveWorkbook.Path & "\*.XLS")
- Do While E <> ""
- If E <> ThisWorkbook.Name Then
- .AddItem E
- End If
- E = Dir
- Loop
- .Value = ActiveWorkbook.Name
- End With
- End Sub
- Private Sub Ū¨ú°lÂÜÀÉ()
- Dim fs As Object, xlWord As Variant, I, E
- Set fs = CreateObject("Scripting.FileSystemObject")
- Set fs = fs.OpenTextFile(xlFile, 1, False)
- xlWord = fs.readall
- fs.Close
- xlWord = Split(xlWord, Chr(10))
- With ListBox2
- .Clear
- .AddItem
- For E = 0 To .ColumnCount - 1
- .List(.ListCount - 1, E) = Split(xlWord(0), ",")(E)
- Next
- End With
- With ListBox1
- .Clear
- For I = 1 To UBound(xlWord) - 1
- If InStr(xlWord(I), ComboBox1 & "]") Then
- .AddItem
- For E = 0 To .ColumnCount - 1
- .List(.ListCount - 1, E) = Split(xlWord(I), ",")(E)
- Next
- End If
- Next
- End With
- End Sub
½Æ»s¥N½X ´¡¤J ¤@¯ë¼Ò²Õ (¦WºÙ¬° Module1 ) ªºµ{¦¡½X- '´¡¤J ¤@¯ë¼Ò²Õ (¦WºÙ¬° Module1 ) ªºµ{¦¡½X
- Option Explicit
- Public Const °lÂÜÀÉ = "\¸ê®Æ¬ö¿ýÀÉ.TXT"
- Public Ar(), xlId As CommandBarControl
- Dim My_App As New Class1
- Private Sub AUTO_CLOSE()
- Application.CommandBars.ActiveMenuBar.Reset
- End Sub
- Private Sub AUTO_Open()
- ª«¥ó³]©w
- ·s¼W«ü¥O
- End Sub
- Private Sub ª«¥ó³]©w()
- Set My_App.APP = Application
- End Sub
- Private Sub ·s¼W«ü¥O()
- With Application.CommandBars.ActiveMenuBar
- .Reset '«³] ¥\¯àªí
- With .Controls.add(10, , , , True) 'msoControlPopup
- .Caption = "°lÂÜ«ü¥O(&P)"
- .TooltipText = "«ö Alt + P"
- Set xlId = .Controls.add(1) 'msoControlButton
- With xlId
- .Caption = "¸ê®Æ¬ö¿ýÀÉ(&C)"
- .OnAction = "°lÂÜ«ü¥O"
- .TooltipText = "«ö Alt + C"
- End With
- With .Controls.add(1) 'msoControlButton
- .Caption = "«ü¥O«³](&R)"
- .OnAction = "AUTO_Open"
- .TooltipText = "«ö Alt + R"
- End With
- End With
- End With
- MakeAr
- End Sub
- Sub °lÂÜ«ü¥O()
- UserForm1.Show
- End Sub
- Private Sub MakeAr() '¨ú±o¤u§@ªí¸ê®Æ
- Dim ArCell As String, Msg As Boolean
- If Workbooks.Count = 0 Then
- Msg = True
- ElseIf ActiveWorkbook.Name = ThisWorkbook.Name Or ActiveWorkbook.Path = "" Then
- Msg = True
- ElseIf Dir(ActiveWorkbook.Path & °lÂÜÀÉ) = "" Then
- Msg = True
- End If
- If Msg = True Then
- xlId.Enabled = False '°±¤î ¸ê®Æ¬ö¿ýÀÉ
- Exit Sub
- Else
- xlId.Enabled = True '«ì´_ ¸ê®Æ¬ö¿ýÀÉ
- End If
- ArCell = Range("A1:" & Cells.SpecialCells(xlCellTypeLastCell).Address).Address
- On Error Resume Next
- Ar = Application.Transpose(Range(ArCell).Value)
- If Err.Number > 0 Then
- ReDim Ar(1, 1)
- Err.Clear
- End If
- End Sub
½Æ»s¥N½X ´¡¤J ª«¥óÃþ§O¼Ò²Õ(¦WºÙ Class1 ) ªºµ{¦¡½X- '´¡¤J ª«¥óÃþ§O¼Ò²Õ(¦WºÙ Class1 ) ªºµ{¦¡½X
- Option Explicit
- Public WithEvents APP As Application
- Private Sub APP_SheetChange(ByVal Sh As Object, ByVal Target As Range)
- Dim xlErr As Integer, xlUsed As String
- If ActiveWorkbook.Name = ThisWorkbook.Name Then Exit Sub
- If ActiveWorkbook.Path = "" Then
- MsgBox ActiveWorkbook.Name & " ¥¼¦sÀÉ ½Ð¥ý¦sÀÉ !"
- Exit Sub
- End If
- xlUsed = Application.UserName
- On Error GoTo R
- If Target(1).Row <= UBound(Ar, 2) And Target(1).Column <= UBound(Ar, 1) And Ar(Target(1).Column, Target(1).Row) <> "" Then
- ¬ö¿ý°lÂÜ Format(Now, "YYYY/MM/DD HH:NN:SS") & "," & Target(1).Address(0, 0, , 1, 1) & "," & Ar(Target.Column, Target(1).Row) & "," & Target(1).Value & "," & Application.UserName
- Else
- ¬ö¿ý°lÂÜ Format(Now, "YYYY/MM/DD HH:NN:SS") & "," & Target(1).Address(0, 0, , 1, 1) & ",," & Target(1) & " ," & Application.UserName
- End If
- R:
- xlErr = Err.Number
- Run "Module1.MakeAr" 'MakeAr
- If xlErr <> 0 Then
- If Dir(ActiveWorkbook.Path & °lÂÜÀÉ) = "" Then
- ¬ö¿ý°lÂÜ Format(Now, "YYYY/MM/DD HH:NN:SS") & "," & Target(1).Address(0, 0, , 1, 1) & "," & Target(1) & ", ," & Application.UserName
- xlId.Enabled = True
- Else
- ¬ö¿ý°lÂÜ Format(Now, "YYYY/MM/DD HH:NN:SS") & "," & Target(1).Address(0, 0, , 1, 1) & "," & Ar(Target.Column, Target(1).Row) & ", ," & Application.UserName
- End If
- End If
- Err.Clear
- Sh.Parent.Save
- End Sub
- Private Sub APP_SheetActivate(ByVal Sh As Object)
- Run "Module1.MakeAr" 'MakeAr
- End Sub
- Private Sub APP_WorkbookActivate(ByVal Wb As Workbook)
- Run "Module1.MakeAr" 'MakeAr
- End Sub
- Private Sub ¬ö¿ý°lÂÜ(xlWord As String) '¬ö¿ýÅܧ󪺸ê®Æ
- Dim fs As Object, xltxt As String, xlFile As String
- xlFile = ActiveWorkbook.Path & °lÂÜÀÉ
- xltxt = Dir(xlFile)
- Set fs = CreateObject("Scripting.FileSystemObject")
- Set fs = fs.OpenTextFile(xlFile, 8, True)
- If xltxt = "" Then
- fs.WriteLINE "¤é´Á,¦ì¸m,쥻,Åܧó,קïªÌ"
- End If
- fs.WriteLINE xlWord
- fs.Close
- End Sub
- Private Sub APP_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
- If Wb.Name <> ThisWorkbook.Name Then Run "Module1.MakeAr" 'MakeAr
- End Sub
- Private Sub APP_WorkbookOpen(ByVal Wb As Workbook)
- Run "Module1.MakeAr" 'MakeAr
- End Sub
½Æ»s¥N½X |
|