GBKEE

¦^´_ 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

GBKEE

¦^´_ 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