Hi all
I am a beginner to excel VBA and i would like to lock the cell range B28:L70 in a worksheet called Energy & Utilities based on yes or no answer in the cell range O39 in a worksheet called Boundary Questions.
I would really appreciate any help.
Place this code in the Boundary Questions worksheet module
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Or Target.Address <> "$O$39" Then Exit Sub
Select Case UCase(Target.Value)
Case "YES": Sheets("Energy & Utilities").Range(" B28:L70").Locked = True
Case "NO": Sheets("Energy & Utilities").Range(" B28:L70").Locked = False
Case Else: Exit Sub
End Select
End SubQuoteAdd Excel VBA Code to a Worksheet Module
Some code is Event code, and will run automatically when a specific action occurs in the worksheet For example, if you type in a cell, and press the Enter key, the worksheet has been changed. This would trigger the Worksheet_Change event. Worksheet event code is stored on a worksheet module. To add it to your worksheet, do the following:
- Copy the code that you want to use
- Select the worksheet in which you want the code to run
- Right click on the sheet tab and choose View Code, to open the Visual Basic Editor.Where the cursor is flashing, choose Edit -> Paste
Don’t have an account yet? Register yourself now and be a part of our community!