Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Protected Range?
I have a sheet with formatted rows, and each new row with consistent
formatting is added using a macro. I'd like to protect the sheet but allow the user to edit certain ranges, i.e. the formatted rows. Since the range that the rows exist changes every time a new row is added, the user-editable range needs to also change. Is this possible? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Protected Range?
Some code similar to this might help you. But you'd probably want to use it
in the Worksheet_Change() event so that it only actually does something when the number of rows in your dynamic area changes. How you decide on that is usually the most complex part of doing something like this. Sub DynamicUnlocking() 'assumes that the range you want 'to unlock is in column D and 'starts at row 2, you just don't 'know where it ends, it needs to 'end at the last used cell in 'column D 'assumes worksheet is active Dim unlockRange As Range Set unlockRange = Range("D2:" & _ Range("D" & Rows.Count).End(xlUp).Address) ActiveSheet.Unprotect ' may need to use this if sheet has password 'change "sheetpw" to actual password 'ActiveSheet.Unprotect Password:="sheetpw" 'this next command is going to lock ALL 'cells on the sheet Cells.Locked = True 'now just unlock the ones we 'want unlocked unlockRange.Locked = False 'put sheet back into protected state 'in order to make your protection scheme work ActiveSheet.Protect ' may need to use this if sheet has password 'change "sheetpw" to actual password 'ActiveSheet.Protect Password:="sheetpw" End Sub "Matt" wrote: I have a sheet with formatted rows, and each new row with consistent formatting is added using a macro. I'd like to protect the sheet but allow the user to edit certain ranges, i.e. the formatted rows. Since the range that the rows exist changes every time a new row is added, the user-editable range needs to also change. Is this possible? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range (again) | Excel Discussion (Misc queries) | |||
Protected Range Not Protected | Excel Worksheet Functions | |||
Dynamic Range | Excel Worksheet Functions | |||
Dynamic Range | Charts and Charting in Excel | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel |