Marco doesn't work in protect sheet

matimes

Board Regular
Joined
Dec 21, 2005
Messages
75
I have the set marco below.
However, when I protect my sheet, it can't work. Any way I can resolve the problem? :rolleyes:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("L:L")
' Only look at that range
Select Case LCase(Target)
Case "pre-cancel", "cancel", "decline"
Target.Offset(, 1) = Date
Case "pending", "expire", "pre-expire", "funded"
Target.Offset(, 1) = ""
End Select
End Sub
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Barrie Davidson

MrExcel MVP
Joined
Feb 10, 2002
Messages
2,330
Unprotect the sheet before executing the code and then protect after executing. BTW, if you only want the code to execute if a change is made in column L, try this modified code.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub

' Only look at that range
If Not Intersect(Target, Range("L:L")) Is Nothing Then
    ActiveSheet.Unprotect
    Application.EnableEvents = False
    Select Case LCase(Target)
        Case "pre-cancel", "cancel", "decline"
            Target.Offset(, 1) = Date
        Case "pending", "expire", "pre-expire", "funded"
            Target.Offset(, 1) = ""
    End Select
    ActiveSheet.Protect
    Application.EnableAnimations = True
End If
End Sub

Hope this helps.
 

matimes

Board Regular
Joined
Dec 21, 2005
Messages
75
Opps, Help....I just discover I insert one column and now the status are in M column and I want to stamp date in N column.
I change the orginal marco range (m:m) but it doesn't work. What should I chnage on my orginal marco and what will be the marco for working in protect sheet.?? Thanks Thanks



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("L:L")
' Only look at that range
Select Case LCase(Target)
Case "pre-cancel", "cancel", "decline"
Target.Offset(, 1) = Date
Case "pending", "expire", "pre-expire", "funded"
Target.Offset(, 1) = ""
End Select
End Sub
 

matimes

Board Regular
Joined
Dec 21, 2005
Messages
75
I try to put the code in, and it appear first line for code you new sent me then below the line another set of code but when I try to pick status from list it come out compile error :which say invaid outside procedure

matimes said:
I have the set marco below.
However, when I protect my sheet, it can't work. Any way I can resolve the problem? :rolleyes:



Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
Set rng = Range("L:L")
' Only look at that range
Select Case LCase(Target)
Case "pre-cancel", "cancel", "decline"
Target.Offset(, 1) = Date
Case "pending", "expire", "pre-expire", "funded"
Target.Offset(, 1) = ""
End Select
End Sub
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,047
Office Version
  1. 365
Platform
  1. Windows
What exact code do you have now?
 

Watch MrExcel Video

Forum statistics

Threads
1,118,089
Messages
5,570,152
Members
412,306
Latest member
fabio6
Top