ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] TextBoxÅã¥Ü¦Û°Ê½s¸¹°ÝÃD½Ð±Ð¡S

[µo°Ý] TextBoxÅã¥Ü¦Û°Ê½s¸¹°ÝÃD½Ð±Ð¡S

½Ð±Ð¦U¦ì¤j¤j¡G
¬O§_¥i¥H¦bTextBoxÅã¥ÜA:A³Ì«á¤@­Ó½s¸¹¦A+1
ÂI¿ïCommandButton1«á±NTextBoxÅã¥Üªº¸ê®ÆCOPY¨ì¤u§@ªí1
ªþ¥ó¦p¤U¡G
TEXT.rar (8.78 KB)
ANN

Label¤é´ÁÅã¥Ü ®æ¦¡°ÝÃD½Ð±Ð

½Ð°Ý¦U¦ì¤j¤j¡G
VLOOKUP°Ñ·Ó½s¸¹²£¥Í¤é´Á¡A
¦ý¤é´Áªº®æ¦¡»P¤u§@ªí1ªº®æ¦¡¤£¦P¡A
½Ð°Ý¬O§_¥i¥H§ï¦¨2014/9/5ªº®æ¦¡Åã¥Ü¡C

TEXT2.rar (14.07 KB)
ANN

TOP

½Ð°Ý¦U¦ì¤j¤j¡G
VLOOKUP°Ñ·Ó½s¸¹²£¥Í¤é´Á¡A
¦ý¤é´Áªº®æ¦¡»P¤u§@ªí1ªº®æ¦¡¤£¦P¡A
½Ð°Ý¬O§_¥i¥H§ï¦¨2014/9 ...
ann.liu µoªí©ó 2014-10-28 23:28

¥Î Format ¨ç¼Æ´N¥i¥H¸Ñ¨M :
...
Me.lbl«Ø¥ß¤é´Á = Format(Application.VLookup(Me.cmb½s¸¹, Worksheets("¤u§@ªí1").[½s¸¹], 2, 0) , "yyyy/m/d")
...

TOP

½Ð±Ð¦U¦ì¤j¤j¡G
¬O§_¥i¥H¦bTextBoxÅã¥ÜA:A³Ì«á¤@­Ó½s¸¹¦A+1
ÂI¿ïCommandButton1«á±NTextBoxÅã¥Üªº¸ê®ÆCOP ...
ann.liu µoªí©ó 2014-10-28 22:44

Module1 :
  1. Public iMax%
½Æ»s¥N½X
ThisWorkBook :
  1. Private Sub Workbook_Open()
  2.   Dim lRow&
  3.   Dim sStr$
  4.   
  5.   iMax = 0
  6.   lRow = 2
  7.   Do While Cells(lRow, 1) <> ""
  8.     sStr = Cells(lRow, 1)
  9.     iNum = Val(Mid(sStr, InStr(1, sStr, "-") + 1))
  10.     If iNum >= iMax Then iMax = iNum
  11.     lRow = lRow + 1
  12.   Loop
  13.   UserForm1.Show
  14. End Sub
½Æ»s¥N½X
UserForm1 :
  1. Private Sub cb¿é¤J_Click()

  2.     With Sheets("¤u§@ªí1")
  3.         If Application.WorksheetFunction.CountIf(.Range("a:a"), txt½s¸¹) > 0 Then
  4.             MsgBox "½s¸¹¤w¦s¦b!", vbCritical, "¿ù»~"
  5.             Exit Sub
  6.         End If
  7.         With .Range("A65536").End(xlUp)
  8.            
  9.             .Range("A2") = txt½s¸¹.Text
  10.             iMax = iMax + 1
  11.             txt½s¸¹.Text = "AL-" & Right("000" & iMax + 1, 3)
  12.         End With
  13.     End With
  14. End Sub

  15. Private Sub UserForm_Initialize()
  16.   txt½s¸¹.Text = "AL-" & Right("000" & iMax + 1, 3)
  17. End Sub
½Æ»s¥N½X
TEXT-a.zip (16.77 KB)

TOP

¦^´_ 2# luhpro
¥t¤@¼gªk
UserForm¼Ò²Õ
  1. Option Explicit
  2. Dim D As Object
  3. Private Sub UserForm_Initialize()
  4.     txt½s¸¹ = ½s¸¹
  5. End Sub
  6. Private Sub cb¿é¤J_Click()
  7.     With Sheets("¤u§@ªí1")
  8.         If Application.CountIf(.Range("a:a"), txt½s¸¹) > 0 And txt½s¸¹ <> "" Then
  9.             MsgBox "½s¸¹¤w¦s¦b!", vbCritical, "¿ù»~"
  10.             Exit Sub
  11.         ElseIf UBound(Split(txt½s¸¹, "-")) = 0 Or txt½s¸¹ = "" Then
  12.             MsgBox "½s¸¹ ®æ¦¡ ¿ù»~!", vbCritical, "¿ù»~"
  13.             Exit Sub
  14.         ElseIf UCase(txt½s¸¹) <> "AL-" & Format(Application.Max(D.KEYS) + 1, "000") Then
  15.             MsgBox txt½s¸¹ & " »Ý¬O " & "AL-" & Format(Application.Max(D.KEYS) + 1, "000"), vbCritical, "¿ù»~"
  16.             Exit Sub
  17.         End If
  18.         .Range("A65536").End(xlUp).Cells(2) = txt½s¸¹
  19.         D(Application.Max(D.KEYS) + 1) = ""
  20.     End With
  21. End Sub
  22. Private Sub CommandButton2_Click() 'cb¿é¤J«á,¥iÄ~Äò + 1
  23.     txt½s¸¹ = "AL-" & Format(Application.Max(D.KEYS) + 1, "000")
  24. End Sub
  25. Private Function ½s¸¹() As String
  26.     Dim E As Range
  27.     Set D = CreateObject("SCRIPTING.DICTIONARY")
  28.     For Each E In ¤u§@ªí1.Range("A:A").SpecialCells(xlCellTypeConstants)
  29.         If UBound(Split(E, "-")) > 0 Then D(Val(Split(E, "-")(1))) = ""
  30.     Next
  31.     If D.Count = 0 Then
  32.         ½s¸¹ = "AL-001"
  33.         D(0) = ""
  34.     Else
  35.         ½s¸¹ = "AL-" & Format(Application.Max(D.KEYS) + 1, "000")
  36.     End If
  37. End Function
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

TEXT-a.rar (27.31 KB) ¦^´_ 5# GBKEE

·PÁÂluhpro»PGBKEE¤j¤jªº¦^ÂСA¤G¦ì¤j¤jªº¤èªk³£¬O¥i¥H¨Ï¥Î¡C

¥t¥~­n½Ð±Ðluhpro¤j¤j
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¤j­n¦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")
   ªþÀÉ¡G
¥t¥~­n½Ð±ÐGBKEE¤j¤j
ªí³æ¦pªG­n³sÄò¿é¤J¦n´Xµ§¡A¬O§_¥i¥H¦Û°Ê¶i¦ì©O
1.        Private Sub CommandButton2_Click() 'cb¿é¤J«á,¥iÄ~Äò + 1
2.        txt½s¸¹ = "AL-" & Format(Application.Max(D.KEYS) + 1, "000")
3.        End Sub
ANN

TOP

¦^´_ 6# ann.liu

ªí³æ¦pªG­n³sÄò¿é¤J¦n´Xµ§¡A¬O§_¥i¥H¦Û°Ê¶i¦ì©O
¸Õ¸Õ¬Ý
  1. Private Sub cb¿é¤J_Click()
  2.     With Sheets("¤u§@ªí1")
  3.         If Application.CountIf(.Range("a:a"), txt½s¸¹) > 0 And txt½s¸¹ <> "" Then
  4.             MsgBox "½s¸¹¤w¦s¦b!", vbCritical, "¿ù»~"
  5.             Exit Sub
  6.         ElseIf UBound(Split(txt½s¸¹, "-")) = 0 Or txt½s¸¹ = "" Then
  7.             MsgBox "½s¸¹ ®æ¦¡ ¿ù»~!", vbCritical, "¿ù»~"
  8.             Exit Sub
  9.         ElseIf UCase(txt½s¸¹) <> "AL-" & Format(Application.Max(D.KEYS) + 1, "000") Then
  10.             MsgBox txt½s¸¹ & " »Ý¬O " & "AL-" & Format(Application.Max(D.KEYS) + 1, "000"), vbCritical, "¿ù»~"
  11.             Exit Sub
  12.         End If
  13.         .Range("A65536").End(xlUp).Cells(2) = txt½s¸¹
  14.         D(Application.Max(D.KEYS) + 1) = ""
  15.         txt½s¸¹ = "AL-" & Format(Application.Max(D.KEYS), "000")
  16.     End With
  17. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

GBKEE¤j¤j
¸Õ«áÁÙ¬O¤£¯à¦Û°Ê¶i¸¹
ANN

TOP

¥»©«³Ì«á¥Ñ 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¤j­n¦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©Ê, ¥u­n¦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)
  1. Private Sub cbSheet_Change()
  2.   Dim bNFind As Boolean
  3.   Dim vA
  4.   
  5.   bNFind = True
  6.   For Each vA In ufTar.cbSheet.List
  7.     If vA = ActiveSheet.Name Then bNFind = False
  8.   Next
  9.   If bNFind Then GetSht
  10.   SetForm
  11. End Sub

  12. Private Sub cb¿é¤J2_Click()
  13.   Dim sStr$
  14.   
  15.   sStr = CStr(cbSheet)
  16.   With Sheets(sStr)
  17.       If Application.WorksheetFunction.CountIf(.Range("a:a"), txt½s¸¹2) > 0 Then
  18.           MsgBox "½s¸¹¤w¦s¦b!", vbCritical, "¿ù»~"
  19.           Exit Sub
  20.       End If
  21.       With .Range("A65536").End(xlUp)
  22.          
  23.           .Range("A2") = txt½s¸¹2
  24.           .Range("B2") = txt©m¦W1
  25.           .Range("C2") = txt¤é´Á1
  26.           iMax(vNo(vSh(sStr))) = iMax(vNo(vSh(sStr))) + 1
  27.           txt½s¸¹2.Text = vSh(sStr) & "-" & Right("0000" & iMax(vNo(vSh(sStr))) + 1, 4)
  28.       End With
  29.   End With
  30. End Sub

  31. Private Sub txt¤é´Á1_Change()
  32.   Dim sStr$
  33.   
  34.   sStr = IIf(Hour(Now) > 12, "¤U¤È", "¤W¤È")
  35.   txt¤é´Á1 = Format(Now, "yyyy/m/d " & sStr & Format(Now, " h:mm"))
  36. End Sub

  37. Public Sub UserForm_Initialize()
  38.   Dim sStr$
  39.   Dim vA
  40.   
  41.   GetSht
  42.   sStr = ActiveSheet.Name
  43.   With cbSheet
  44.     .Clear
  45.     For Each vA In vSh
  46.       .AddItem vA
  47.       If vA = sStr Then .Value = sStr
  48.     Next
  49.     If .Value = "" Then
  50.       .ListIndex = 0
  51.     End If
  52.   End With
  53.   SetForm
  54. End Sub

  55. Private Sub SetForm()
  56.   Dim sStr$
  57.   
  58.   Application.CommandBars(1).Controls(iBtn).Visible = False
  59.   sStr = CStr(cbSheet)
  60.   Sheets(sStr).Select
  61.   txt½s¸¹2.Text = vSh(sStr) & "-" & Right("0000" & iMax(vNo(vSh(sStr))) + 1, 4)
  62.   Label2.Caption = Sheets(sStr).[B1]
  63.   txt¤é´Á1 = Format(Now, "yyyy/m/d " & sStr & Format(Now, " h:mm"))
  64. End Sub

  65. Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  66.    Application.CommandBars(1).Controls(iBtn).Visible = True
  67. End Sub
½Æ»s¥N½X
ThisWorkBook
  1. Private Sub Workbook_Open()
  2.   SetButton (False)
  3.   GetSht
  4.   SetButton (True)
  5. End Sub

  6. Private Sub SetButton(bSw As Boolean)
  7.   Dim sName$
  8.   Dim bNFind As Boolean
  9.   Dim vC, tcbLdData
  10.   
  11.   sName = "¶}±Òªí®æ"
  12.   If Not bSw Then
  13.     For Each vC In Application.CommandBars(1).Controls
  14.       With vC
  15.         If .Caption = sName Then
  16.           .Visible = False
  17.           iBtn = .Index
  18.         End If
  19.       End With
  20.     Next
  21.   Else
  22.     bNFind = True
  23.     For Each vC In Application.CommandBars(1).Controls
  24.       With vC
  25.         If .Caption = sName Then
  26.           bNFind = False
  27.           .Visible = True
  28.           iBtn = .Index
  29.         End If
  30.       End With
  31.     Next
  32.   
  33.     If bNFind Then
  34.       Set tcbLdData = Application.CommandBars(1).Controls.Add(Type:=msoControlButton)
  35.       With tcbLdData
  36.         .Caption = sName
  37.         .FaceId = 2778
  38.         .OnAction = "ShowForm"
  39.         iBtn = .Index
  40.       End With
  41.     End If
  42.   End If
  43. End Sub

  44. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  45.   Application.CommandBars(1).Controls(iBtn).Delete
  46. End Sub
½Æ»s¥N½X
Module1
  1. Public iMax%(), iBtn%
  2. Public vNo, vSh

  3. Option Explicit

  4. Public Sub ShowForm()

  5. On Error GoTo ErrShowForm
  6.   With ufTar
  7.     If .Visible = False Then .Show
  8.   End With
  9.   
  10. On Error GoTo 0
  11.   Exit Sub

  12. ErrShowForm:

  13.   Select Case Err.Number
  14.   
  15.     Case 424 ' ¦¹³B»Ý­nª«¥ó
  16.       ufTar.Show
  17.       
  18.       
  19.     Case 13 ' «¬ºA¤£²Å¦X
  20.       Set vSh = CreateObject("Scripting.Dictionary")
  21.       Set vNo = CreateObject("Scripting.Dictionary")
  22.       ReDim iMax(0)
  23.       
  24.     Case Else
  25.       On Error GoTo 0
  26.   End Select
  27.   Resume
  28. End Sub

  29. Public Sub GetSht()
  30.   Dim iNum%
  31.   Dim lRow&
  32.   Dim sStr$
  33.   Dim bNFind1 As Boolean, bNFind2 As Boolean
  34.   Dim vA, vB

  35. On Error GoTo ErrGetSht
  36.   
  37.   bNFind2 = False
  38.   For Each vA In Worksheets
  39.     bNFind1 = True
  40.     For Each vB In vSh
  41.       If vB = vA.Name Then
  42.         bNFind1 = False
  43.         Exit For
  44.       End If
  45.     Next
  46.     If bNFind1 Then
  47.       bNFind2 = True
  48.       Exit For
  49.     End If
  50.   Next
  51.   
  52.   If Not bNFind2 Then
  53.     bNFind2 = False
  54.     For Each vA In vSh
  55.       bNFind1 = True
  56.       For Each vB In Worksheets
  57.         If vB.Name = vA Then
  58.           bNFind1 = False
  59.           Exit For
  60.         End If
  61.       Next
  62.       If bNFind1 Then
  63.         bNFind2 = True
  64.         Exit For
  65.       End If
  66.     Next
  67.   End If
  68.   
  69.   If bNFind2 Then
  70.     GoSub SetSel
  71.   End If

  72. On Error GoTo 0
  73.   Exit Sub


  74. SetSel:
  75.   Set vSh = CreateObject("Scripting.Dictionary")
  76.   Set vNo = CreateObject("Scripting.Dictionary")
  77.   ReDim iMax(0)
  78.   For Each vA In Worksheets
  79.     With vA
  80.       If .Name Like "¤u§@ªí*" Then
  81.         If .[A2] = "" Then .[A2] = "xxx-0001"
  82.         sStr = Left(.[A2], InStrRev(.[A2], "-") - 1)
  83.         vSh(CStr(.Name)) = sStr
  84.         ReDim Preserve iMax(UBound(iMax, 1) + 1)
  85.         iMax(UBound(iMax, 1)) = 0
  86.         vNo(sStr) = UBound(iMax, 1)
  87.   
  88.         lRow = 2
  89.         Do While .Cells(lRow, 1) <> ""
  90.           sStr = .Cells(lRow, 1)
  91.           iNum = Val(Mid(sStr, InStrRev(sStr, "-") + 1))
  92.           If iNum >= iMax(UBound(iMax, 1)) Then iMax(UBound(iMax, 1)) = iNum
  93.           lRow = lRow + 1
  94.         Loop
  95.       End If
  96.     End With
  97.   Next
  98. Return


  99. ErrGetSht:

  100.   Select Case Err.Number
  101.   
  102.     Case 424 ' ¦¹³B»Ý­nª«¥ó
  103.       Set vSh = CreateObject("Scripting.Dictionary")
  104.       Set vNo = CreateObject("Scripting.Dictionary")
  105.       ReDim iMax(0)
  106.    
  107.     Case 13 ' «¬ºA¤£²Å¦X
  108.       Set vSh = CreateObject("Scripting.Dictionary")
  109.       Set vNo = CreateObject("Scripting.Dictionary")
  110.       ReDim iMax(0)
  111.    
  112.     Case Else
  113.       On Error GoTo 0
  114.   End Select
  115.   Resume
  116. End Sub
½Æ»s¥N½X
TEXT-a.zip (25.55 KB)

TOP

·PÁÂluhpro¤j¤j¡G
§Aªº¼gªk´ú¸Õ«á¡A¯u¬OÅý§Ú¤Ó¨ØªA¤F¡AÁöµMµ{¦¡¤£¬O«Ü¤F¸Ñ¡A
¦ý·|§V¤Oªº¬ã¨s¡AÀÉ®×´N¥ý¦¬¤U¡AºCºC¬ã¨s¤F¡A·P®¦¡C
ANN

TOP

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD