Solved

Running a macro when the value of a cell changes

Posted on 2014-04-25
7
718 Views
Last Modified: 2014-04-26
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.
0
Comment
Question by:Allpurple
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:Allpurple
ID: 40022910
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 40024379
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 40024383
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 14

Expert Comment

by:Faustulus
ID: 40024401
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
 

Author Closing Comment

by:Allpurple
ID: 40024457
Thanks for your input, I see that looks like a shorter code so will use it at the first opportunity. Best regards
0
 

Author Comment

by:Allpurple
ID: 40024461
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
 
LVL 14

Expert Comment

by:Faustulus
ID: 40024513
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

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

A high-level exploration of how our ever-increasing access to information has changed the way we do our jobs.
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now