|
发表于 2012-1-15 10:29
|
显示全部楼层
本楼为最佳答案
本帖最后由 sunjing-zxl 于 2012-1-15 10:33 编辑
- Sub 下拉菜单()
- Dim arr
- Dim i As Long, str As String
- Dim d As New dictionary
- With Sheets("数据库")
- arr = .Range("D1:D" & .[D65536].End(xlUp).row)
- End With
- For i = 1 To UBound(arr)
- d(arr(i, 1)) = d(arr(i, 1)) + 1
- Next i
- arr = d.Keys()
- str = Join(arr, ",")
- With Sheets("录入数据").Range("C2").Validation
- .Delete
- .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
- xlBetween, Formula1:=str
- End With
- Set d = Nothing
- End Sub
- '这段代码放到模块里面
复制代码- Private Sub Worksheet_Change(ByVal Target As Range) '自动添加日期
- If Target.Column = 4 Then
- Call 下拉菜单
- End If
- If Target.Column = 4 Then
- Target.Offset(, -3) = VBA.Year(Date)
- Target.Offset(, -2) = VBA.Month(Date)
- Target.Offset(, -1) = VBA.Day(Date)
- End If
- End Sub
- '上面这个在你原有代码里面加三句代码
复制代码 附件:
求助2012工时统计-sunjing.rar
(146.03 KB, 下载次数: 33)
|
|