|
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
本帖最后由 甘地降价 于 2024-4-3 09:34 编辑
最佳的方式,是设置基于sheet的私有代码。
比较方便。也契合楼主的要求。
Sub SetUpDropDownList()
Dim ws As Worksheet
Dim dv As Validation
Dim list As Variant
' 设置工作表对象
Set ws = ThisWorkbook.Sheets("Sheet1") ' 替换为你的工作表名
' 定义下拉菜单的列表项
list = Array("千元1", "千元2", "万元1", "万元2", "元") ' 替换为你的选项列表
' 清除任何现有的数据验证
With ws.Range("A2").Validation ' 假设你想在A1单元格设置下拉菜单
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Join(list, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = "选择"
.ErrorTitle = "输入错误"
.InputMessage = "请从列表中选择一个选项。"
.ErrorMessage = "必须选择一个有效的选项。"
.ShowInput = True
.ShowError = True
End With
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2")) Is Nothing Then ' 替换为你的下拉菜单单元格范围
' 在这里添加处理下拉菜单选项更改的代码
Dim selectedValue As String
selectedValue = Target.Value
Set Rng = Union(ThisWorkbook.Sheets("Sheet2").Range("C6:D78"), ThisWorkbook.Sheets("Sheet2").Range("G6:H78"))
For Each cell In Rng
cellValue = cell.Value
' 根据下拉菜单的选项执行不同的操作
If IsNumeric(cellValue) Then
Select Case selectedValue
Case "千元1"
cell.Value = cell.Value
cell.NumberFormat = "0.00,""千元"""
Case "千元2"
cell.Value = cell.Value
cell.NumberFormat = "0,""千元"""
Case "万元1"
cell.Value = cell.Value
cell.NumberFormat = "0!.00,""万元"""
Case "万元2"
cell.Value = cell.Value
cell.NumberFormat = "0!.00,""万元"""
Case "元"
cell.Value = cell.Value
cell.NumberFormat = "0.00"
Case Else
' 处理其他情况或未定义的值
MsgBox "未知选项"
End Select
Else
cell.Value = ""
End If
Next cell
End If
End Sub
|
|