时间戳

首页 > 解决方案 > 锁定单元格并用数据验证/VBA 替换内容

excel - 锁定单元格并用数据验证/VBA 替换内容

问题描述

E5 是一个下拉菜单，有以下 3 个选项：无、自耗、隔夜充电

  1. 选择“无”时，需要清除单元格 F37、F41:H41、F50:H51​​ 中的内容并锁定单元格。
  2. 选择“自耗”时，F37需要解锁，F41:H41、F50:H51​​需要清零锁定。
  3. 选择“通宵充电”时，F37需要清零锁定，F41:H41、F50:H51​​需要解锁。

E4 是一个复选框，如果取消选中，则需要清除和锁定以下单元格：F25:J25、F26、F28:J29、F31:J31。

我保存了以下宏来清除和锁定单元格。请有人让我知道我接下来要做什么来添加上述条件。非常感谢。

Sub Solar()
'
' Solar Macro
' clear and lock cells if no panels being installed
'

'
    Range("F25:J25,F26,F28:J29,F31:J31").Select
    Range("F31").Activate
    Selection.ClearContents
    Selection.Locked = True
    Selection.FormulaHidden = False
End Sub
Sub Battery_SC()
'
' Battery_SC Macro
' Clear and lock cell if no self-consumption battery being installed
'

'
    Range("F37").Select
    ActiveCell.FormulaR1C1 = "0%"
    Range("F37").Select
    Selection.Locked = True
    Selection.FormulaHidden = False
End Sub

Sub Battery_OC()
'
' Battery_OC
' Clear and lock cells if no overnight battery being installed
'

'
    Range("F41:H41,F50:H50,F51:H51").Select
    Range("F51").Activate
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-9
    Selection.Locked = True
    Selection.FormulaHidden = False
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$5" Then
        If Target.Value = "None" Then
            Range("F37,F41:H41,F50:H50,F51:H51").Select
            Range("F51").Activate
            Selection.ClearContents
            Selection.Locked = True
            Selection.FormulaHidden = False
        End If
        If Target.Value = "Self-Consumption" Then
            Range("F41:H41,F50:H50,F51:H51").Select
            Range("F51").Activate
            Selection.ClearContents
            Selection.Locked = True
            Selection.FormulaHidden = False
        End If
        If Target.Value = "Overnight Charging" Then
            Range("F37").Select
            Selection.ClearContents
            Selection.Locked = True
            Selection.FormulaHidden = False
        End If
    End If
End Sub

标签： excelvbaexcel-formula

解决方案

您正在尝试执行的操作称为“工作表更改”事件。当指定单元格（称为“目标”）发生更改时，请执行某些操作。这是大纲，您需要填写“执行某些操作”部分。

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$E$5" Then
        If Target.Value = "None" Then
            Cells(1, 1) = 1
        End If
        If Target.Value = "Self-Consumption" Then
            Cells(1, 1) = 2
        End If
        If Target.Value = "Overnight Charging" Then
            Cells(1, 1) = 3
        End If
    End If
End Sub

@Darrell - 这是错的吗？

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$E$5" Then
    If Target.Value = "None" Then
        With Range("F37,F41:H41,F50:H50,F51:H51")
             .ClearContents
             .Locked = True
             .FormulaHidden = False
        End With
    End If
    If Target.Value = "Self-Consumption" Then
        With Range("F41:H41,F50:H50,F51:H51")
             .ClearContents
             .Locked = True
             .FormulaHidden = False
        End With
    End If
    If Target.Value = "Overnight Charging" Then
        With Range("F37")
             .ClearContents
             .Locked = True
             .FormulaHidden = False
        End With
    End If
End If

结束子

推荐阅读