Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection in VBA.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection in VBA.
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 Auto_Open. That Auto_Open procedure will run each time the workbook is opened (and the user allows macros to run). MAX wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Protection in VBA.
Thanks for your help.
"MAX" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Data Protection Best Practice: AKA: Real Sheet Protection | Excel Discussion (Misc queries) | |||
Excel Data Protection- AKA: Sheet/Macro Password Protection | Setting up and Configuration of Excel | |||
WS Protection: Different Levels of Protection on Different Ranges | Excel Discussion (Misc queries) | |||
Cell Protection vs. Worksheet Protection | Excel Discussion (Misc queries) | |||
Worksheet protection is gone and only wokbook protection can be se | Excel Discussion (Misc queries) |