- ©«¤l
- 835
- ¥DÃD
- 6
- ºëµØ
- 0
- ¿n¤À
- 915
- ÂI¦W
- 15
- §@·~¨t²Î
- Win 10,7
- ³nÅ骩¥»
- 2019,2013,2003
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- µù¥U®É¶¡
- 2010-5-3
- ³Ì«áµn¿ý
- 2024-11-12
|
¥»©«³Ì«á¥Ñ luhpro ©ó 2014-11-2 22:52 ½s¿è
1.¦pªG§Ú¦³4ÓUserForm ³£n¦Û°Ê½s¸¹¡A½s¸¹³£n¦Û°Ê±a¥X¡A
§Ú«·s»s§@¥t¥~¤TÓªí³æ¡A¦ý¸¹½X·|¶Ã¸õ¡A¤£ª¾¿ù»~¦b¨º¡A½Ð¤j¤j«ü±Ð¡S
2.§Ú°µ¤F4Ó«ö¶sÂI¿ï»Ýnªºªí³æ¡A¤£n¶}©lexcel´N¦Û°Ê¸õ¥Xµøµ¡¡A
½Ð°Ý¤j¤jn¦p¦ó½s¼g©O¡S
3.¤é´ÁÅã¥Ü¥i¥H¥[¤W®É¶¡¶Ü¡S
Me.lbl«Ø¥ß¤é´Á = Format(Application.VLookup(Me.cmb½s¸¹, Worksheets("¤u§@ªí1").[½s¸¹], 2, 0) , "yyyy/m/d") ...
ann.liu µoªí©ó 2014-10-31 09:36
§Ú«°µ¤F.
1. §R°£ ¶}±Òªí®æ ¤u§@ªí, §R°£¨ä¥L¦hªº UserForm, ¥u«O¯d¤@Ó UserForm ¹ê²{¬ÛÃö¥\¯à, «O¯d¤é«á·s¼W¤u§@ªíªº¼u©Ê, ¥un¦WºÙ¶}ÀY¬° "¤u§@ªí" ´N·|¦Û°Ê¥[¤J¿ï¾Ü§Ç¦C, ¥Ø«e¥i¦b Userform ¤Wª½±µ¤Á´«¤u§@ªí»P¼W¸¹.
2. ±Ò°Ê UserForm «ö¶s©ñ¦b¤W¤è ¥\¯àªí°Ï ¤¤ »¡©ú ªº¥kÃä, ¨ºÓ«ö¶sÂI¤@¤U§Y¥i¶}±Ò UserForm.
3. ¦]¬°§A¨S¦³»¡©ú¤é´Á®É¶¡¨úȪº¼Ð·Ç,§Úª½±µ±a¤J¥Ø«e®É¶¡.
4. ¦b¤u§@ªí¿ï³æ¤Á´«®É, ¦U¤u§@ªí¤¤ B1 ªºÈ·|±a¨ì ©m¦W ¼ÐÃDÄæ, «O¯d¼u©Ê½Õ¾ãÅã¥Üªº¥i¯à©Ê.
5. ·s¼Wªº¤u§@ªí¶·¦Û¦æ¿é¤J A1,B1,C1 ªº¼ÐÃD¦WºÙ, A2 «h¿é¤J·sªº½s¸¹
6. ³¡¤À¦a¤è¥i¯àÁÙ¦³ BUG ,®É¶¡¤£°÷¨Ó¤£¤Î¤@¤@´ú¸Õ.
ufTar (UserForm)- Private Sub cbSheet_Change()
- Dim bNFind As Boolean
- Dim vA
-
- bNFind = True
- For Each vA In ufTar.cbSheet.List
- If vA = ActiveSheet.Name Then bNFind = False
- Next
- If bNFind Then GetSht
- SetForm
- End Sub
- Private Sub cb¿é¤J2_Click()
- Dim sStr$
-
- sStr = CStr(cbSheet)
- With Sheets(sStr)
- If Application.WorksheetFunction.CountIf(.Range("a:a"), txt½s¸¹2) > 0 Then
- MsgBox "½s¸¹¤w¦s¦b!", vbCritical, "¿ù»~"
- Exit Sub
- End If
- With .Range("A65536").End(xlUp)
-
- .Range("A2") = txt½s¸¹2
- .Range("B2") = txt©m¦W1
- .Range("C2") = txt¤é´Á1
- iMax(vNo(vSh(sStr))) = iMax(vNo(vSh(sStr))) + 1
- txt½s¸¹2.Text = vSh(sStr) & "-" & Right("0000" & iMax(vNo(vSh(sStr))) + 1, 4)
- End With
- End With
- End Sub
- Private Sub txt¤é´Á1_Change()
- Dim sStr$
-
- sStr = IIf(Hour(Now) > 12, "¤U¤È", "¤W¤È")
- txt¤é´Á1 = Format(Now, "yyyy/m/d " & sStr & Format(Now, " h:mm"))
- End Sub
- Public Sub UserForm_Initialize()
- Dim sStr$
- Dim vA
-
- GetSht
- sStr = ActiveSheet.Name
- With cbSheet
- .Clear
- For Each vA In vSh
- .AddItem vA
- If vA = sStr Then .Value = sStr
- Next
- If .Value = "" Then
- .ListIndex = 0
- End If
- End With
- SetForm
- End Sub
- Private Sub SetForm()
- Dim sStr$
-
- Application.CommandBars(1).Controls(iBtn).Visible = False
- sStr = CStr(cbSheet)
- Sheets(sStr).Select
- txt½s¸¹2.Text = vSh(sStr) & "-" & Right("0000" & iMax(vNo(vSh(sStr))) + 1, 4)
- Label2.Caption = Sheets(sStr).[B1]
- txt¤é´Á1 = Format(Now, "yyyy/m/d " & sStr & Format(Now, " h:mm"))
- End Sub
- Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
- Application.CommandBars(1).Controls(iBtn).Visible = True
- End Sub
½Æ»s¥N½X ThisWorkBook- Private Sub Workbook_Open()
- SetButton (False)
- GetSht
- SetButton (True)
- End Sub
- Private Sub SetButton(bSw As Boolean)
- Dim sName$
- Dim bNFind As Boolean
- Dim vC, tcbLdData
-
- sName = "¶}±Òªí®æ"
- If Not bSw Then
- For Each vC In Application.CommandBars(1).Controls
- With vC
- If .Caption = sName Then
- .Visible = False
- iBtn = .Index
- End If
- End With
- Next
- Else
- bNFind = True
- For Each vC In Application.CommandBars(1).Controls
- With vC
- If .Caption = sName Then
- bNFind = False
- .Visible = True
- iBtn = .Index
- End If
- End With
- Next
-
- If bNFind Then
- Set tcbLdData = Application.CommandBars(1).Controls.Add(Type:=msoControlButton)
- With tcbLdData
- .Caption = sName
- .FaceId = 2778
- .OnAction = "ShowForm"
- iBtn = .Index
- End With
- End If
- End If
- End Sub
- Private Sub Workbook_BeforeClose(Cancel As Boolean)
- Application.CommandBars(1).Controls(iBtn).Delete
- End Sub
½Æ»s¥N½X Module1- Public iMax%(), iBtn%
- Public vNo, vSh
- Option Explicit
- Public Sub ShowForm()
- On Error GoTo ErrShowForm
- With ufTar
- If .Visible = False Then .Show
- End With
-
- On Error GoTo 0
- Exit Sub
- ErrShowForm:
- Select Case Err.Number
-
- Case 424 ' ¦¹³B»Ýnª«¥ó
- ufTar.Show
-
-
- Case 13 ' «¬ºA¤£²Å¦X
- Set vSh = CreateObject("Scripting.Dictionary")
- Set vNo = CreateObject("Scripting.Dictionary")
- ReDim iMax(0)
-
- Case Else
- On Error GoTo 0
- End Select
- Resume
- End Sub
- Public Sub GetSht()
- Dim iNum%
- Dim lRow&
- Dim sStr$
- Dim bNFind1 As Boolean, bNFind2 As Boolean
- Dim vA, vB
- On Error GoTo ErrGetSht
-
- bNFind2 = False
- For Each vA In Worksheets
- bNFind1 = True
- For Each vB In vSh
- If vB = vA.Name Then
- bNFind1 = False
- Exit For
- End If
- Next
- If bNFind1 Then
- bNFind2 = True
- Exit For
- End If
- Next
-
- If Not bNFind2 Then
- bNFind2 = False
- For Each vA In vSh
- bNFind1 = True
- For Each vB In Worksheets
- If vB.Name = vA Then
- bNFind1 = False
- Exit For
- End If
- Next
- If bNFind1 Then
- bNFind2 = True
- Exit For
- End If
- Next
- End If
-
- If bNFind2 Then
- GoSub SetSel
- End If
- On Error GoTo 0
- Exit Sub
- SetSel:
- Set vSh = CreateObject("Scripting.Dictionary")
- Set vNo = CreateObject("Scripting.Dictionary")
- ReDim iMax(0)
- For Each vA In Worksheets
- With vA
- If .Name Like "¤u§@ªí*" Then
- If .[A2] = "" Then .[A2] = "xxx-0001"
- sStr = Left(.[A2], InStrRev(.[A2], "-") - 1)
- vSh(CStr(.Name)) = sStr
- ReDim Preserve iMax(UBound(iMax, 1) + 1)
- iMax(UBound(iMax, 1)) = 0
- vNo(sStr) = UBound(iMax, 1)
-
- lRow = 2
- Do While .Cells(lRow, 1) <> ""
- sStr = .Cells(lRow, 1)
- iNum = Val(Mid(sStr, InStrRev(sStr, "-") + 1))
- If iNum >= iMax(UBound(iMax, 1)) Then iMax(UBound(iMax, 1)) = iNum
- lRow = lRow + 1
- Loop
- End If
- End With
- Next
- Return
- ErrGetSht:
- Select Case Err.Number
-
- Case 424 ' ¦¹³B»Ýnª«¥ó
- Set vSh = CreateObject("Scripting.Dictionary")
- Set vNo = CreateObject("Scripting.Dictionary")
- ReDim iMax(0)
-
- Case 13 ' «¬ºA¤£²Å¦X
- Set vSh = CreateObject("Scripting.Dictionary")
- Set vNo = CreateObject("Scripting.Dictionary")
- ReDim iMax(0)
-
- Case Else
- On Error GoTo 0
- End Select
- Resume
- End Sub
½Æ»s¥N½X
TEXT-a.zip (25.55 KB)
|
|