Solved: Unprotect Work sheet to allow Macro to run

[BENSON]

I thought I knew this one but apparently not .If the work sheet "TS SP" refered to in the code below is protected, where would I need to insert the code "Work Sheet unprotect" are some other code to unprotect the work sheet "TS SP"

THANKS


[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
Dim RowNum As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

RowNum = Application.Match(.Value, Worksheets("TS sp").Columns(1), 0)
Worksheets("TS sp").Rows(RowNum).SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("TS SP").Rows(RowNum).Cut
Worksheets("TS SP").Rows(38).Insert Shift:=xlDown
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub
[/VBA]

[Aussiebear]

Try the following code

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B:B" '<== change to suit
Dim RowNum As Long

On Error GoTo ws_exit
Application.EnableEvents = False
Sheets("TS SP").UnProtect password:="password"
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

RowNum = Application.Match(.Value, Worksheets("TS sp").Columns(1), 0)
Worksheets("TS sp").Rows(RowNum).SpecialCells(xlCellTypeConstants).ClearContents
Worksheets("TS SP").Rows(RowNum).Cut
Worksheets("TS SP").Rows(38).Insert Shift:=xlDown
End With
End If

ws_exit:
Sheets("TS SP").Protect password:="password"
Application.EnableEvents = True
End Sub
[/vba]

[HaHoBe]

Hi, BENSON,

why not set the protection with UserInterfaceOnly:=True on opening or activating the sheet to allow any macro code to be executed?

Ciao,
Holger