Macro not working (2000)

Topic

New Reply

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

Reply | Quote

Replies

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 Worksheet_Calculate or the Worksheet_SelectionChange events to handle this. Your code will be the same, just put them in these two events.

WARNING: The Worksheet_Calculate 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 Worksheet_SelectionChange 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

Reply | Quote

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

Reply | Quote

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:

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

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.

Reply | Quote

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.

Reply | Quote

If you changed the A2:A2000 to D2:D2000, 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:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
    Application.EnableEvents = False
    If Not Intersect(Target, ActiveSheet.Range("D2:D2000")) Is Nothing Then
        On Error Resume Next
        ActiveSheet.Unprotect
        On Error GoTo 0
        For Each oCell In Intersect(Target, ActiveSheet.Range("D2:D2000"))
            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

Reply | Quote

Once again Mr. Coleman, you have come through for me!
One quick question to help me understand this a little better…in your example can “Target” (as in “If Not Intersect(Target, ActiveSheet.Range(“D2:D2000″)) 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

Reply | Quote

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 D2:D2000. If this is Nothing (an empty range), then none of the cells in D2:D2000 were changed. This may or may not include the ActiveCell. For example, if the user copied cells C2:D3 and pasted them into C4:D5, then ActiveCell would be C4 and the Intersect method would return D4:D5 which does not include the ActiveCell.

Reply | Quote

I understand. Thanks for the explanation.

Reply | Quote

Stats

You said your code works?

I ask because this troubles me: If “A2” = “UNPLANNED” Then …

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

OK about the Looping. You only want to test if <<>> 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

Reply | Quote

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

Reply | Quote