Thread: Code to run macros on protected sheets

Apologies - I thought by typing them in, it would have the same effect - apparently not, please excuse my ignorance.

Greed:

(1) You can type your code tags. I think you initially closed your code tag with [\code] instead of [/code]. (Note the orientation of the slash.

(2) In order to run code on a protected sheet, you have to think in turns of chronology. So, you would have to first unprotect the sheet, then run the code, then reprotect it. Without doing any troubleshooting, your code should read like the following:

Sub ProtectUIOnly()
ActiveSheet.Unprotect Password:="mypassword"
    Dim Values As Range
    Dim cell As Variant
    Set Values = Range("A3:F28")
    For Each cell In Values
        If cell.Value = "OT" Or cell.Value = "A/P/C" _
        Or cell.Value = "A/C" Or cell.Value = "DE-IN" Then
            cell.Font.ColorIndex = 10
                End If
                Next cell       
ActiveSheet.Protect Password:="mypassword", userinterfaceonly:=True
End Sub

Let me know if this works and if there is anything else with which I can help.

Yes! That's just the thing, and I thank you for the explanation - I did have to alter the button to make it work as I was initially getting another error, but it works now so that's great.

I feel like an idiot not noticing the / vs \ in my code tags though....

Can anyone explain why the 'Application.Volatile' portion of my code isn't working for me? Is it because I have made an error, or because it is not effective on cells that are drop-downs perhaps?

Thanks again!

Application.Volatile, to my understanding, applies more to user defined functions, essentially forcing a recaculation whenever a change is made to a spreadsheet. It can also slow down the functionality of the spreadsheet.

In your case, I think you should do some research on Worksheet_Change events. These are placed into the worksheet code instead of a standard module, and can trigger a macro based on a change. For example, if you added this code directly to the worksheet module, it would run your macro ANYTIME a change is made to cell A1 in the corresponding sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
     ActiveSheet.Unprotect Password:="mypassword"
    Dim Values As Range
    Dim cell As Variant
    Set Values = Range("A3:F28")
    For Each cell In Values
        If cell.Value = "OT" Or cell.Value = "A/P/C" _
        Or cell.Value = "A/C" Or cell.Value = "DE-IN" Then
            cell.Font.ColorIndex = 10
                End If
                Next cell       
ActiveSheet.Protect Password:="mypassword", userinterfaceonly:=True
   
    End If
End Sub

Thanks, the explanation helps. I will continue to study up on my Excel skills - sometimes I wish I didn't have a job so I would have more time to learn I find it truely fascinating...

A1 is a locked cell that never changes. With the code you provided, am I able to replace $A$1 with a range instead?

Greed,

The TARGET refers to the cell that is being changed, so it can never truly be a range of cells. There are ways around this. For example, instead of returning the address of the target, you can search the column of the target. To trigger the macro anytime a change is made to any cell in column B, the code would read:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
     ActiveSheet.Unprotect Password:="mypassword"
    Dim Values As Range
    Dim cell As Variant
    Set Values = Range("A3:F28")
    For Each cell In Values
        If cell.Value = "OT" Or cell.Value = "A/P/C" _
        Or cell.Value = "A/C" Or cell.Value = "DE-IN" Then
            cell.Font.ColorIndex = 10
                End If
                Next cell       
ActiveSheet.Protect Password:="mypassword", userinterfaceonly:=True
   
    End If
End Sub

If you want to run a macro whenever there is a change to a number of cells, you can use UNION. The following code will trigger your macro whenever you make a change to any cells in range A4:F25:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Union(Target, Range("A4:F25")) Is Nothing Then
        ActiveSheet.Unprotect Password:="mypassword"
        
        Dim Values As Range
        Dim cell As Variant
        Set Values = Range("A3:F28")
        
        For Each cell In Values
            If cell.Value = "OT" Or cell.Value = "A/P/C" _
                Or cell.Value = "A/C" Or cell.Value = "DE-IN" Then
                cell.Font.ColorIndex = 10
            End If
        Next cell
        
        ActiveSheet.Protect Password:="mypassword", userinterfaceonly:=True
    End If
End Sub

Thanks - unfortunately the 2nd code (referring to UNION) does not function as expected...the cells do not change colour once the corresponding codes (A/C, OT etc.) are selected - again, not sure if this is because the text is selected from a drop-down list? No worries though, the insertion of the unprotect/protect code is enabling the macro button so I'm happy!