Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell protection
Is it possible to protect multiple cell ranges for different reasons? I
would like to protect certain cells to not allow any changes and I would like to protect other ranges to allow data entry but not change the cell formatting. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell protection
Gregg Johnson wrote:
Is it possible to protect multiple cell ranges for different reasons? I would like to protect certain cells to not allow any changes and I would like to protect other ranges to allow data entry but not change the cell formatting. Thanks. Hi Gregg As far as I know, when protecting the worksheet, you can protect all the cells from having their formats changed or none of them. But try FormatConditional Formatting of the cells where you want to allow editing but not format change e.g. In cell A1: Formula Is =OR(A1="",A1<"") Apply the Format you want. This should override a format applied by a user as long as the cell contains nothing or something! Regards Steve |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell protection
Steve -
Thank you for your response. I'm having a little trouble with it: When I attempt to change the format in conditional formatting, it will not allow me to select Font or Size, only Style and Color. Is this typical? Users will paste information into these cells. When I did that, it removed the conditional formatting and used the pasted format. Is that to be expected? Thanks. "Scoops" wrote: Gregg Johnson wrote: Is it possible to protect multiple cell ranges for different reasons? I would like to protect certain cells to not allow any changes and I would like to protect other ranges to allow data entry but not change the cell formatting. Thanks. Hi Gregg As far as I know, when protecting the worksheet, you can protect all the cells from having their formats changed or none of them. But try FormatConditional Formatting of the cells where you want to allow editing but not format change e.g. In cell A1: Formula Is =OR(A1="",A1<"") Apply the Format you want. This should override a format applied by a user as long as the cell contains nothing or something! Regards Steve |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell protection
Gregg Johnson wrote: Steve - Thank you for your response. I'm having a little trouble with it: When I attempt to change the format in conditional formatting, it will not allow me to select Font or Size, only Style and Color. Is this typical? Users will paste information into these cells. When I did that, it removed the conditional formatting and used the pasted format. Is that to be expected? Thanks. Hi Gregg Yes, that's to be expected in both cases. As a quick solution (I'm just about to finish for the day and this may be the proverbial sledgehammer), try this: Right-click the tab of the appropriate sheet View Code and paste this: Private Sub Worksheet_Change(ByVal Target As Range) Dim myValue With Application .EnableEvents = False myValue = Target.Value .Undo Target = myValue .EnableEvents = True End With End Sub Regards Steve |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell protection
Wow - that works great. Thanks.
"Scoops" wrote: Gregg Johnson wrote: Steve - Thank you for your response. I'm having a little trouble with it: When I attempt to change the format in conditional formatting, it will not allow me to select Font or Size, only Style and Color. Is this typical? Users will paste information into these cells. When I did that, it removed the conditional formatting and used the pasted format. Is that to be expected? Thanks. Hi Gregg Yes, that's to be expected in both cases. As a quick solution (I'm just about to finish for the day and this may be the proverbial sledgehammer), try this: Right-click the tab of the appropriate sheet View Code and paste this: Private Sub Worksheet_Change(ByVal Target As Range) Dim myValue With Application .EnableEvents = False myValue = Target.Value .Undo Target = myValue .EnableEvents = True End With End Sub Regards Steve |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cell protection
Gregg Johnson wrote: Wow - that works great. Thanks. Glad to help, just be aware that the macro will work on every cell in the worksheet so, if you do have some cells that you would like to be altered by a paste action, you'll need to restrict where the macro works. For example, if you do not want it to affect cells in column A or row 2 or cell B3 then something like: Private Sub Worksheet_Change(ByVal Target As Range) Dim myValue If Target.Column = 1 Or _ Target.Row = 2 Or _ Target.Address = "$B$3" Then Exit Sub With Application .EnableEvents = False myValue = Target.Value .Undo Target = myValue .EnableEvents = True End With End Sub Regards Steve |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compiling macro based on cell values | Excel Discussion (Misc queries) | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Data validation, cell protection or other method? | Excel Discussion (Misc queries) | |||
up to 7 functions? | Excel Worksheet Functions | |||
cell protection | Excel Discussion (Misc queries) |