+ Reply to Thread
Results 1 to 2 of 2

ClearContents when the worksheet is protected

  1. #1
    Registered User
    Join Date
    02-15-2006
    Posts
    18

    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?

  2. #2
    Henrich
    Guest

    RE: 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
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1