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?
Bookmarks