Option Explicit
Dim d, k, t, d1 'A列自动生成序号(1)
Dim crr, i& ', tms 'A列自动生成序号(1)
Private Sub Worksheet_Change(ByVal targetAs Range) 'A列自动生成序号(3)
If target.Count > 1 Then Exit Sub
If target.Column = 3 And target.Row > 2Then
If target = "" Thentarget.Offset(0, -2) = "": GoTo 10
Dim rq$, mc$, n, n1, xh
rq = Right(Year(Date), 2) &Format(Month(Date), "00") & Format(Day(Date), "00")
mc = target.Value
If d.exists(rq) Then
xh = d(rq)
If d1.exists(rq & "|" & mc) Then
n1 = d1(rq & "|" & mc)
target.Offset(0, -2) = crr(n1, 1)
Else
target.Offset(0, -2) = "HX" & rq & Format(xh + 1,"00")
End If
Else
target.Offset(0, -2) = "HX" & rq & "01"
End If
10:
target.Offset(0, 1).Select
End If
End Sub
Private Sub Worksheet_SelectionChange(ByValtarget As Range) 'A列自动生成序号(2)
If target.Count > 1 Then Exit Sub
If target.Column <> 3 Or target.Row< 3 Then Exit Sub
Dim aa$, xh
Set d =CreateObject("Scripting.Dictionary")
Set d1 =CreateObject("Scripting.Dictionary")
crr = Sheet12.UsedRange
For i = 3 To UBound(crr)
aa = Mid(crr(i, 1), 3, 6)
xh = Val(Right(crr(i, 1), 2))
If xh > d(aa) Then d(aa) = xh
d1(aa & "|" & crr(i, 3)) = i
Next
k = d.keys: t = d.items
End Sub
录入窗体代码如下:
Private Sub 添加_Click()
Dim a As Integer
Dim i As Long
Dim myControl As Control
Set d = CreateObject("Scripting.Dictionary")
arr = Sheets("资料库").Range("b3:c" & Sheets("资料库").[c999].End(3).Row)
For i = 1 To UBound(arr)
d(arr(i, 1) & arr(i, 2)) = i
Next
If d.exists(客户类别.Value & 客户名称.Value) Then
CreateObject("WScript.Shell").Popup "该客户已存在!", 1, "提示" '提示框延时
Unload Me
Exit Sub
End If
a = Sheets("资料库").[b65536].End(xlUp).Row '取得行号
With Sheets("资料库").Range("A" & a + 1) '找到存档表最后一行下的空行
' .Value = 日期.Value '号码
.Offset(0, 1).Value = 客户类别.Value
.Offset(0, 2).Value = 客户名称.Value
.Offset(0, 3).Value = 职务.Value
.Offset(0, 4).Value = 姓名.Value
.Offset(0, 5).Value = 移动电话.Value
.Offset(0, 6).Value = 业务电话.Value
.Offset(0, 7).Value = 电子邮件.Value
.Offset(0, 8).Value = 开户银行.Value
.Offset(0, 9).Value = 帐号.Value
.Offset(0, 10).Value = 地址.Value
.Offset(0, 11).Value = 备注.Value
With Sheets("出入库")
Sheet6.Unprotect Password:="lidayu" '触发单元格即取消工作表保护
For Each myControl In Me.Controls
If InStr(myControl.Name, "客户名称") Then
If Len(myControl.Name) > 4 Then
If myControl.Value = "" Then Exit For
n = n + 1
i = .[x1].CurrentRegion.Rows.Count + 1
.Cells(i, 24) = myControl.Text
If n = 1 Then
.Cells(i, 25) = Me.Controls("combobox1").Text
.Cells(i, 26) = Me.Controls("产品名称").Text
.Cells(i, 27) = Me.Controls("销售价格").Text
Else
.Cells(i, 25) = Me.Controls("combobox" & n).Text
.Cells(i, 26) = Me.Controls("产品名称" & n).Text
.Cells(i, 27) = Me.Controls("销售价格" & n).Text
End If
End If
End If
Next
End With
CreateObject("WScript.Shell").Popup "您已保存成功!", 1, "提示" '提示框延时
Sheet6.Protect Password:="lidayu", DrawingObjects:=True, Contents:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFormattingCells:=True
Unload Me '卸载 我(窗体)
End With
End Sub