Running a macro when the value of a cell changes

I have made an amateurish attempt at getting a macro to run when the value of a cell changes. There is a small dropdown list with three choices, "Choose", "Yes", "No". When "Yes" is chosen I want a selection of cells to become unprotected, thereby allowing the user access. I start with a sheet specific Private Sub as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$42" Then
Application.EnableEvents = False
Unlock_Cells01
Application.EnableEvents = True
End If
End Sub

When cell $B$42 is changed, the macro Unlock_Cells01 runs, but I have the macro wrong as it doesn't work with the variable I have attempted to build in. Without the variables, whatever I choose from the dropdown list activates the macro and unlocks the chosen cells. Here is my attempt:
Sub Unlock_Cells01()
'
' Unlock_Cells01 Macro
'

'
    Sheets("Rental Form").Select
    ActiveSheet.Unprotect
    If B42 = "Yes" Then
    Range("B44:C47").Select
    Selection.Locked = False
    End If
    Else
    If B42 = "No" Then
    Range("B44:C47").Select
    Selection.Locked = True
    End If
    Else
    If B42 = "Choose" Then
    Range("B44:C47").Select
    Selection.Locked = True
    End If
    Selection.FormulaHidden = False
    ActiveSheet.Protect
End Sub

Please tell me where I've messed up.

Thank-you in advance.
Bob BarnesAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
You can reduce this to

Sub Unlock_Cells01()
'
' Unlock_Cells01 Macro
'
    Sheets("Rental Form").Select
    ActiveSheet.Unprotect
    Range("B44:C47").Select
    If Range("B42") = "Yes" Then
        Selection.Locked = False
    ElseIf Range("B42") = "No" or Range("B42") = "Choose" Then
        Selection.Locked = True
    End If
    Selection.FormulaHidden = False
    ActiveSheet.Protect
End Sub
0
 
Bob BarnesAuthor Commented:
Sorry, but I have managed to solve the problem as follows:
Sub Unlock_Cells01()
'
' Unlock_Cells01 Macro
'

'
    Sheets("Rental Form").Select
    ActiveSheet.Unprotect
    If Range("B42") = "Yes" Then
    Range("B44:C47").Select
    Selection.Locked = False
    ElseIf Range("B42") = "No" Then
    Range("B44:C47").Select
    Selection.Locked = True
    ElseIf Range("B42") = "Choose" Then
    Range("B44:C47").Select
    Selection.Locked = True
    End If
    Selection.FormulaHidden = False
    ActiveSheet.Protect
End Sub

Thanks anyway
0
 
Saqib Husain, SyedEngineerCommented:
Further reduction; not sure if it works


Sub Unlock_Cells01()
'
' Unlock_Cells01 Macro
'
    Sheets("Rental Form").Select
    ActiveSheet.Unprotect
    Range("B44:C47").Locked = not(Range("B42") = "Yes")
    Selection.FormulaHidden = False
    ActiveSheet.Protect
End Sub
0
Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

 
FaustulusCommented:
In the entire operation, you don't need to select anything. Not doing so will save you from a lot of imprecision. For example:-
    Sheets("Rental Form").Select
    ActiveSheet.Unprotect

Open in new window

Why do you select the Sheet "Rental Form"? After selecting it it will become the ActiveSheet. As a matter of fact, in this case, it also was the ActiveSheet before. But if you wish to be sure to do your stuff on this particular sheet you should just go ahead and do it, not on the ActiveSheet but on Sheets("Rental Form") - and never mind whether it is active or not.

What is supposed to happen if B42 is neither "Yes", nor "No", nor "Choose"?
Your code will do nothing, just leave the Locked property the way it is. Most likely, it is True (locked), but it isn't in the spirit of your code to leave it as it is. Your code is very clear:-
If the answer is "Yes" unlock the range. In all other cases make sure that it is locked. Accordingly, you only need to check if it is "Yes" and ignore all others.

In the process, it isn't necessary to select the range and then address the Selection object. You can address the range directly. No need to select anything. So, you arrive at the following code.
Sub Unlock_Cells01()
    
    With Sheets("Rental Form")
        .Unprotect
        .Range("B44:C47").Locked = Not (.Range("B42").Value = "Yes")
        .Range("B44:C47").FormulaHidden = False
        .Protect
    End With
End Sub

Open in new window

If the Sub Unlock_Cells01 is to be called by other procedures it would be good practice to place it in a standard code module the way you have done. However, if it is to be used only with the event procedure in Sheet("Rental Form") it would be better off in the sheet's own code module or even integrated in the event procedure itself. That would look like this:-
Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Address = "$B$42" Then
        Application.EnableEvents = False
        .Worksheet.Unprotect
        Range("B44:C47").Locked = Not (.Value = "Yes")
        Range("B44:C47").FormulaHidden = False
        .Worksheet.Protect
        Application.EnableEvents = True
    End With
End Sub

Open in new window

Placing the code here would put paid to all questions of which sheet the action is carried out on allowing for more simplification in the syntax.

I hope this is still interesting to you even though the actual problem was already solved.

Faustulus
0
 
Bob BarnesAuthor Commented:
Thanks for your input, I see that looks like a shorter code so will use it at the first opportunity. Best regards
0
 
Bob BarnesAuthor Commented:
Thank-you Faustulus, I didn't see your reply until after I had accepted the previous response and allocated the points but I am sure yours will work when I try it out on Monday.
Best regards
0
 
FaustulusCommented:
That's OK and fair enough. My post took too long to prepare. Anyway, Saqib saw the shorter logic too. He didn't spot the ActiveSheet, though. Just imagine, if you could hide the fact from him, are you surprised that you became confused yourself? Writing code is like writing prose: choose the exact words to convey your meaning, don't allow get side-tracked, and don't repeat yourself.
Faustulus
0
All Courses

From novice to tech pro — start learning today.