Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro not working (2000)

    I have a worksheet that has most of the cells protected, but if a user types in the word "UNPLANNED" in Col. A2, as an example, I want to unprotect B2, C2 & F2. I came up with the macro below but it doesn't seem to work, the cells B2, C2 & F2 remain protected.

    Public Sub UnprotectSheets()
    If "A2" = "UNPLANNED" Then
    ActiveSheet.UNPROTECT
    Range("B2,C2,F2").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    ActiveSheet.Protect
    End If

    End Sub

    Also, I want this macro to work for cells A2 to A2000 so if in A100 they type "UNPLANNED" then B100, C100 and F100 will become unprotected. I thought I'd try and get the first part to work and then tackle getting the macro to work for the rest of the cells but I can't get that far.
    Thanks for any help that can be offered.
    Stats

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro not working (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Stats

    OK two quick questions:
    1) Is the worksheet protected before the user starts typing?
    2) Is Column A2:Axxx protected?

    If so you will need to have at least an initial cell unprotected say A2 so that the user can enter a value. And then the code will open another cell in column A and it keeps going on until the user is done.

    If Column A is not protected, try and use the <font color=blue> Worksheet_Calculate </font color=blue> or the <font color=blue> Worksheet_SelectionChange </font color=blue> events to handle this. Your code will be the same, just put them in these two events.

    <font color=red> WARNING: </font color=red> The <font color=blue> Worksheet_Calculate </font color=blue> will fire each and every time the user enters a letter, so you want to have this wait until the user has entered enough letters to spell "UNPLANNED"

    The <font color=blue> Worksheet_SelectionChange </font color=blue> will fire when you move the selection, so you need to make sure it is in Column A before doing anything.

    Let me know if you need any explanations.

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    Thank you very much, I changed the macro (see below), can you please tell me how I would change it so the same thing will happen if "UNPLANNED" is typed in any cell from A2 to A2000, so if A100 has "UNPLANNED" then cells B100, C100 and F100 will become unprotected.

    Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
    Select Case Target.Address
    Case "$A$1"
    If Range("A1") = "UNPLANNED" Then
    Application.ActiveSheet.UNPROTECT
    Range("B1,C1,F1").Select
    Selection.Locked = False
    Selection.FormulaHidden = False
    Application.ActiveSheet.Protect
    End If
    End Select
    End Sub

    Thanks again for your quick help.
    Stats

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    You will need to use the Worksheet Change event rather than the Selection Change. The Selection Change Target argument is the cell that you are changing to, not the one you just left, and that is not what you want to look at.

    I believe the following code will do what you asked:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Application.EnableEvents = False
    If Not Intersect(Target, ActiveSheet.Range("A2:A2000")) Is Nothing Then
    On Error Resume Next
    ActiveSheet.Unprotect
    On Error GoTo 0
    For Each oCell In Intersect(Target, ActiveSheet.Range("A2:A2000"))
    If UCase(oCell.Value) = "UNPLANNED" Then
    With Union(Range(oCell.Offset(0, 1), oCell.Offset(0, 2)), oCell.Offset(0, 5))
    .Locked = False
    .FormulaHidden = False
    End With
    End If
    Next oCell
    End If
    ActiveSheet.Protect
    Application.EnableEvents = True
    End Sub
    </pre>


    You might also want to consider protecting the cells if the user enters anything other than "UNPLANNED" into the cells in A2:A2000. The code above may eventually result in all of the cells in B, C, and F being unprotected.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    Thanks Legare,
    Unfortunatly I am unable to get it to work. The cells remain locked (and there is no error message). In my example I said it was col. A but it is actually col. D so I changed the range and the cols I wanted unlocked are G,H & I so I changed the offset reference to (0,3), (0,4)), (0,5)) keeping the brackets in the right spot. I have doubled checked and I can't find anything I might have copied wrong. Any advise on how I debug this.
    Also, in regards to your last point, I do have validation required for col. D.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    If you changed the A2:A2000 to D22000, then the offsets are from column D not Column A, so they should be 1, 2, and 3. Also, if the columns being unprotected are contiguous, then the Union method is not needed which simplifies the code. It should look like this:

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Application.EnableEvents = False
    If Not Intersect(Target, ActiveSheet.Range("D22000")) Is Nothing Then
    On Error Resume Next
    ActiveSheet.Unprotect
    On Error GoTo 0
    For Each oCell In Intersect(Target, ActiveSheet.Range("D22000"))
    If UCase(oCell.Value) = "UNPLANNED" Then
    With Range(oCell.Offset(0, 1), oCell.Offset(0, 3))
    .Locked = False
    .FormulaHidden = False
    End With
    End If
    Next oCell
    End If
    ActiveSheet.Protect
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

  7. #7
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Macro not working (2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Stats

    You said your code <font color=red>works</font color=red>?

    I ask because this troubles me: <font color=blue>If "A2" = "UNPLANNED" Then </font color=blue>...

    I think you need something like If Range("A2").Value = "UNPLANNED" Then... You see the string "A2" will never be equal to "UNPLANNED". So Please explain to me how your code is working <img src=/S/blush.gif border=0 alt=blush width=15 height=15> <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

    OK about the Looping. You only want to test if <<< A2 to A2000 >>> has "UNPLANNED"? What about "Unplanned", or "unPlanned", or UnPlanned"? All are considered the same?

    OK here is the code that does what you want, and you can change it as needed. I tried to document it as best as I can without being too obvious, so use it as a startup point, and if you need any help, let me know.

    Wassim
    Attached Files Attached Files
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  8. #8
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    Thanks Wassim,
    The explanations you included really helped me understand how these macro puzzles go together. I think you may have misread my post though, the IF("A2")="UNPLANNED" was in my first post and I said that it, of course, was not working.
    Thankis for your input.
    Stats

  9. #9
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    Once again Mr. Coleman, you have come through for me! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    One quick question to help me understand this a little better...in your example can "Target" (as in "If Not Intersect(Target, ActiveSheet.Range("D22000")) Is Nothing Then") be "considered" the same thing as "ActiveCell"? What I mean is; does the Intersect command look at where the target and the range meet and determine that is the cell in question?
    Thanks again for all of your help.
    Stats

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    Target can be a range of more than one cell, which is why I use the For Each statement further down in the code. This can happen if the user copies several cells and then pastes them onto the worksheet, or if the user fills more than one cell. The Intersect method returns a range that consists of all of the cells that are in all of the ranges specified in the arguments. In this case, it returns all of the cells that were in Target (the cells that were changed) that are also in D22000. If this is Nothing (an empty range), then none of the cells in D22000 were changed. This may or may not include the ActiveCell. For example, if the user copied cells C23 and pasted them into C45, then ActiveCell would be C4 and the Intersect method would return D45 which does not include the ActiveCell.
    Legare Coleman

  11. #11
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Trenton, Ontario
    Posts
    175
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro not working (2000)

    I understand. Thanks for the explanation.

Posting Permissions

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