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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
What exact code do you have now?
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

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