ClearContents when the worksheet is protected
 

I have a bubble set to when it is clicked it will delete "# N/A" from
every cell containing that in a column. When the worksheet is protected
I have it fixed so everything works except that the "#N/A" still shows
up when I copy it into my column. here is the source for one of the
buttons.

Private Sub Usersel_Click()
With Range("H:H")
..ClearContents
..FormatConditions.Delete
End With
Range("p7:p5500") = "=IF(B7="""","""",ISNUMBER(MATCH(B7,D:D,0)))"
Range("q7:q5500") = "=IF(P7="""",NA(),IF(p7=TRUE,B7,NA()))"

Application.ScreenUpdating = False

On Error Resume Next
Range("q7:q5500").SpecialCells(xlFormulas, xlErrors).ClearContents
On Error GoTo 0
Range("q7:q5500").Copy
Range("H7:H5500").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Range("a1").Select

Range("H7:H5500").Sort Key1:=Range("H7"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Application.ScreenUpdating = True
End Sub

The red line is the only lines in that code that doesn't work while the
sheet is protected. so when I get it into column H it will have the
names from my other columns plus a ton of "#N/A"s and I want to be
able to have users edit the columns(which they can) and have access to
all the sorting stuff but not edit anything else. Any Ideas?


--
Xiazer
------------------------------------------------------------------------
Xiazer's Profile: http://www.excelforum.com/member.php...o&userid=31581
View this thread: http://www.excelforum.com/showthread...hreadid=520045

ClearContents when the worksheet is protected
 

Hi, why you didn't unprotect the sheet just for the moment when the code is
started and at the end of the code make it again protected?
PS: The user will not see this because you have turned off screen updating.

Henrich

€žXiazer" napÃ*sal (napÃ*sala):


I have a bubble set to when it is clicked it will delete "# N/A" from
every cell containing that in a column. When the worksheet is protected
I have it fixed so everything works except that the "#N/A" still shows
up when I copy it into my column. here is the source for one of the
buttons.

Private Sub Usersel_Click()
With Range("H:H")
.ClearContents
.FormatConditions.Delete
End With
Range("p7:p5500") = "=IF(B7="""","""",ISNUMBER(MATCH(B7,D:D,0)))"
Range("q7:q5500") = "=IF(P7="""",NA(),IF(p7=TRUE,B7,NA()))"

Application.ScreenUpdating = False

On Error Resume Next
Range("q7:q5500").SpecialCells(xlFormulas, xlErrors).ClearContents
On Error GoTo 0
Range("q7:q5500").Copy
Range("H7:H5500").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
Range("a1").Select

Range("H7:H5500").Sort Key1:=Range("H7"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

Application.ScreenUpdating = True
End Sub

The red line is the only lines in that code that doesn't work while the
sheet is protected. so when I get it into column H it will have the
names from my other columns plus a ton of "#N/A"s and I want to be
able to have users edit the columns(which they can) and have access to
all the sorting stuff but not edit anything else. Any Ideas?


--
Xiazer
------------------------------------------------------------------------
Xiazer's Profile: http://www.excelforum.com/member.php...o&userid=31581
View this thread: http://www.excelforum.com/showthread...hreadid=520045