PDA

View Full Version : Solved: Unprotect Work sheet to allow Macro to run



BENSON
07-01-2009, 12:08 AM
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



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

Aussiebear
07-01-2009, 12:32 AM
Try the following code


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

HaHoBe
07-01-2009, 05:19 AM
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