Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color of protected cells programatically
I am not sure if the problem is directly related to upgrading to Excel 2003,
or a setting in Excel which has been changed due to the upgrade. The app is full of macro's as well as having VBA code. All cells are protected except for those used for user-input. The cell in question is a calculated value. If it exceeds a certain value it must change from red to blue. This is done in VBA. This has always worked, until the upgrade to 2003. Is there a security setting which can be used to allow VBA to change the font color of the protected cell, or am I going to have to use VBA to unprotect the cell, change the font color, and protect the cell again? Nigel |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color of protected cells programatically
I don't know what (if anything) changed between xl2003 and your previous
version, but this sounds like it's the perfect situation to use format|conditional formatting. You wouldn't need VBA at all (for the color change). Nigel wrote: I am not sure if the problem is directly related to upgrading to Excel 2003, or a setting in Excel which has been changed due to the upgrade. The app is full of macro's as well as having VBA code. All cells are protected except for those used for user-input. The cell in question is a calculated value. If it exceeds a certain value it must change from red to blue. This is done in VBA. This has always worked, until the upgrade to 2003. Is there a security setting which can be used to allow VBA to change the font color of the protected cell, or am I going to have to use VBA to unprotect the cell, change the font color, and protect the cell again? Nigel -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color of protected cells programatically
"Dave Peterson" wrote:
I don't know what (if anything) changed between xl2003 and your previous version, but this sounds like it's the perfect situation to use format|conditional formatting. You wouldn't need VBA at all (for the color change). Could do that - problem is that then 25 copies of the modified spreadsheet must be distributed around the country!! That is why I am hoping for a settings solution. Also, why did it used to work. And will conditional fromatting work with older versions of Excel, for the people who haven't upgraded yet? Nigel |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color of protected cells programatically
What version of excel where you using?
What was the code that did the actual work? Did you unprotect or protect the worksheet to allow the program to do things that the user can't (using the userinterfaceonly parm on the .protect statement)? Did the cell get locked on that protected sheet? ====== xl2007 changed conditional formatting quite a bit. Before that, all versions of excel that supported conditional formatting (xl97-xl2003???) were the same. Nigel wrote: "Dave Peterson" wrote: I don't know what (if anything) changed between xl2003 and your previous version, but this sounds like it's the perfect situation to use format|conditional formatting. You wouldn't need VBA at all (for the color change). Could do that - problem is that then 25 copies of the modified spreadsheet must be distributed around the country!! That is why I am hoping for a settings solution. Also, why did it used to work. And will conditional fromatting work with older versions of Excel, for the people who haven't upgraded yet? Nigel -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color of protected cells programatically
Please remember that this has always worked. This app has been used since
about 1997, through many upgrades, and many versions of Excel, probably starting with '95. Although the font colour change was not originally there - a letr innovation. I also have vague recollections of the problem occuring with one or two users at various times, but I have no ide how the problem was solved in those cases. The version of the app with the font colour change has been running ob '98, '2000 and for all I know there are useres with '2003 who don't have the problem. I am not fully up to date as I retired the end of last year and it is only one user who contacted me about it. Checking on my own PC I can replicate the problem. The code snippet is: If Range("SchedHrs").Offset(X, 1).Value < "" Then If Range("SchedHrs").Offset(X, 1).Value < Cells(4, 66).Value Then Range("SchedHrs").Offset(X, 1).Font.Color = vbRed Else Range("SchedHrs").Offset(X, 1).Font.Color = vbBlue End If Admittedly, there is a chance that on the copies where the code does work, that the worksheet in question is not protected. Unprotecting the sheet makes the code work. Also, when protecting the sheet, if I allow the user to format cells, then the code works. NB It seems to be that Excel applies the protection level of formatting cells to changes made by code, as well as by the user. The reference to Cell(4,66) is in any case incorrect, so the code doesn't really work as is anyway, so they will have to modify the code and redistribute the templates anyway. Which probably means that I will have to do it, as I don't think there is anyone in the company that has ever used VBA!!! Nigel |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color of protected cells programatically
Another thought has come to me: People were using the template just fine.
They made a backup of the template, were given new PC's with new versions of Office, put the template back into XLStart, and it no longer works. Has to be either Excel 2003, or a setting. There are changes to Excel in the way of using templates so it could very well be 2003's problem. Nigel |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Changing font color of protected cells programatically
I don't have another guess.
Nigel wrote: Another thought has come to me: People were using the template just fine. They made a backup of the template, were given new PC's with new versions of Office, put the template back into XLStart, and it no longer works. Has to be either Excel 2003, or a setting. There are changes to Excel in the way of using templates so it could very well be 2003's problem. Nigel -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing Font color based on font type or size | Excel Discussion (Misc queries) | |||
Change font color in protected worksheet | Excel Worksheet Functions | |||
Changing Font Color | Excel Discussion (Misc queries) | |||
changing font color for new text | Excel Discussion (Misc queries) | |||
How to enable font color on protected worksheet? | Excel Discussion (Misc queries) |