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

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