VBA Excel实战

    

为了帮朋友实现Excel中的一个功能，接触了VBA Excel。历经两天的时间，现将最终结果存档。

参考资料

vba excel编程三日谈

实现功能

在Excel文档中，当第一列为“是”，则右边6个单元格不可编辑且背景色改变；当第一列为“否”，则右边6个单元格可编辑且背景色恢复白色。

准备工作

新建一个Excel文档，选中第一列，按照下图设置数据有效性：

代码

Private Sub Worksheet_Change(ByVal Target As range)        If Target.Column = 1 Then            Dim editable As Boolean        Dim rowNumber As Integer        Dim columnNumber As Integer        Dim rag As range                        rowNumber = Target.Row        columnNumber = Target.Column        Set rag = range(Cells(rowNumber, columnNumber + 1), Cells(rowNumber, columnNumber + 6))                                    If Target.Text = "是" Then                       editable = False             rag = ""            With rag.Interior                .Pattern = xlSolid                .PatternColorIndex = xlAutomatic                .ThemeColor = xlThemeColorDark1                .TintAndShade = -0.149998474074526                .PatternTintAndShade = 0            End With                    End If                If Target.Text = "否" Then              editable = True                        With rag.Interior                .Pattern = xlNone                .TintAndShade = 0                .PatternTintAndShade = 0            End With                End If                With rag.Validation            .Delete            .Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Operator:= _            xlBetween, Formula1:=editable            .IgnoreBlank = True            .InCellDropdown = True            .InputTitle = ""            .ErrorTitle = ""            .InputMessage = ""            .ErrorMessage = ""            .IMEMode = xlIMEModeNoControl            .ShowInput = True            .ShowError = True        End With            End If    End Sub