Worksheet_Change Problem

David20

Board Regular
Joined
May 7, 2009
Messages
57
I'm trying to use Worksheet_Change to unlock certain cells when I change the customer name in the target (C7). When I actually change the name in C7, it responds correctly. However, it also runs the code whenever I press "Enter", regardless if I changed the target cell or not. How do I restrict it to running ONLY when C7 is changed and NOT when I enter data somewhere else on the worksheet?

Here is my code:

Code:
Sub Worksheet_Change(ByVal Target As Range)
'
' CustomerChange Macro
'
    Dim VRange As Range
    Set VRange = Range("'Instructions'!C7")
    If VRange Is Nothing Then
        Sheets("Instructions").Unprotect
        Sheets("Instructions").Range("C23:C27,G7,G8,G21,G29,G30").Locked = True
        Selection.FormulaHidden = False
        Sheets("Instructions").Protect AllowFiltering:=True, AllowUsingPivotTables:=True
        Exit Sub
    ElseIf VRange = "Customer 1" Then
        ActiveSheet.Unprotect
        Range("C23:C27").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
        ActiveSheet.Protect AllowFiltering:=True, AllowUsingPivotTables:=True
        Exit Sub
    ElseIf Range("F7") <> "" Then
        ActiveSheet.Unprotect
        Range("G7").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    ElseIf Range("F8") <> "" Then
        ActiveSheet.Unprotect
        Range("G7").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    ElseIf Range("F21") <> "" Then
        ActiveSheet.Unprotect
        Range("G21").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    ElseIf Range("F29") <> "" Then
        ActiveSheet.Unprotect
        Range("G29").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    ElseIf Range("F30") <> "" Then
        ActiveSheet.Unprotect
        Range("G30").Select
        Selection.Locked = False
        Selection.FormulaHidden = False
    Else
        ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFiltering:=True, AllowUsingPivotTables:=True
    End If
End Sub

Thanks in advance.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Try adding this as the first line

Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$7" Then Exit Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,226,737
Messages
6,192,737
Members
453,753
Latest member
SORD

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top