Protection in VBA.

[MAX]

Question 1.
I have a code (below), that blinks certain cells in a workbook where it works
perfect, but when I protect each sheet it stops working. (Protect without
giving a password, just click on protect worksheet)
Is there a solution by modifying the code? Please if there is a solution,
will you please arrange the code for me, cause I am a beginner in VBA.

Question 2.
Can someone give me the colour codes. ( example 3 is for RED).

Thanks a lot.

Workbook code:
Private Sub Workbook_Open()
StartBlink
StartBlink2
StartBlink3
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
StopBlink
StopBlink2
StopBlink3
End Sub

Module Code:

Public RunWhen As Double

Sub StartBlink()
With ThisWorkbook.Worksheets("Serie A").Range("AN6,AW6").Font
If .ColorIndex = 3 Then ' Red Text
..ColorIndex = 31 ' White Text
Else
..ColorIndex = 3 ' Red Text
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink", , True
End Sub

Sub StopBlink()
ThisWorkbook.Worksheets("Serie A").Range("AN6,AW6").Font.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, "StartBlink", , False
End Sub


Sub StartBlink2()
With ThisWorkbook.Worksheets("Serie B").Range("AN6,AW6").Font
If .ColorIndex = 3 Then ' Red Text
..ColorIndex = 2 ' White Text
Else
..ColorIndex = 3 ' Red Text
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink2", , True
End Sub

Sub StopBlink2()
ThisWorkbook.Worksheets("Serie B").Range("AN6,AW6").Font.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, "StartBlink2", , False
End Sub
Sub StartBlink3()
With ThisWorkbook.Worksheets("Serie C").Range("AN6,AW6,AN83,AW83").Font
If .ColorIndex = 3 Then ' Red Text
..ColorIndex = 2 ' White Text
Else
..ColorIndex = 3 ' Red Text
End If
End With
RunWhen = Now + TimeSerial(0, 0, 1)
Application.OnTime RunWhen, "StartBlink3", , True
End Sub

Sub StopBlink3()
ThisWorkbook.Worksheets("Serie
C").Range("AN6,AW6,AN83,AW83").Font.ColorIndex = _
xlColorIndexAutomatic
Application.OnTime RunWhen, "StartBlink3", , False
End Sub

 

[Dave Peterson]

There are things you (and your code) can't do when the worksheet is protected.

One way around it is to unprotect the worksheet, then do the work, then
reprotect the worksheet.

ThisWorkbook.Worksheets("Serie A").Unprotect 'password:="yourpasswordhere"

With ThisWorkbook.Worksheets("Serie A").Range("AN6,AW6").Font
If .ColorIndex = 3 Then ' Red Text
.ColorIndex = 31 ' White Text
Else
.ColorIndex = 3 ' Red Text
End If
End With

ThisWorkbook.Worksheets("Serie A").protect 'password:="yourpasswordhere"

=======
You'll need to do this for every routine that changes the color.

===============
Another option is to protect the worksheet in code. There's a setting that you
can specify that allows your code to do some/most things that users can't when
the worksheet is protected.

Add this to a general module:

Option Explicit
Sub auto_open()
With Worksheets("Serie A")
.Protect userinterfaceonly:=True 'password:=.....
End With
End Sub

This setting isn't remembered between opening/closings of the workbook. That's
why the code goes in a general module in a procedure named Autpen. That
Autpen procedure will run each time the workbook is opened (and the user
allows macros to run).

 

[MAX]

Thanks for your help.