Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect hidden rows/columns -macro help
I have financial reports that have hidden rows columns once generated. When
data is selected, the hidden rows/columns data is still selected when pasted. Can someone suggest a macro that can be run to protect/thus lock and prevent selection of hidden rows and columns? Thanks! =) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect hidden rows/columns -macro help
To add further clarification to what I'm looking for the scenario I was
thinking is this: 1)If I knew the attribute to a row/column that is hidden, I could use it in the macro, and then LOCK those rows/columns that were hidden. 2) Then, I would Protect the sheet with password and allow selection on only the unlocked rows/columns. Is this fairly simple? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect hidden rows/columns -macro help
Not too sure exactly what you are trying to do but here's some code
that might help. This will look through all the rows in the range(I used range A1 to A10) and if a row is hidden it will lock it. Sub FindHiddenAndLock() Dim mcell, MyRange As Range Set MyRange = Range("A1:A10") For Each mcell In MyRange If mcell.Rows.Hidden = True Then mcell.Rows.Locked = True End If Next End Sub Sandy ttbbgg wrote: To add further clarification to what I'm looking for the scenario I was thinking is this: 1)If I knew the attribute to a row/column that is hidden, I could use it in the macro, and then LOCK those rows/columns that were hidden. 2) Then, I would Protect the sheet with password and allow selection on only the unlocked rows/columns. Is this fairly simple? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect hidden rows/columns -macro help
This seems to work really well, but now the user cannot do a continuous
selection of what cells are unlocked. Could we change this requirement of locking the cells to just deleting the entire row and column that is already hidden? Would the code go as follows? If mcell.Rows.Hidden = True Then DELETE Please note that I have hidden columns as well that I'd like removed. "Sandy" wrote: Not too sure exactly what you are trying to do but here's some code that might help. This will look through all the rows in the range(I used range A1 to A10) and if a row is hidden it will lock it. Sub FindHiddenAndLock() Dim mcell, MyRange As Range Set MyRange = Range("A1:A10") For Each mcell In MyRange If mcell.Rows.Hidden = True Then mcell.Rows.Locked = True End If Next End Sub Sandy ttbbgg wrote: To add further clarification to what I'm looking for the scenario I was thinking is this: 1)If I knew the attribute to a row/column that is hidden, I could use it in the macro, and then LOCK those rows/columns that were hidden. 2) Then, I would Protect the sheet with password and allow selection on only the unlocked rows/columns. Is this fairly simple? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protect hidden rows/columns -macro help
This is just deleting the first cell in the hidden row. I need the entire
row deleted so as to shift all the unhidden rows up. "Sandy" wrote: Not too sure exactly what you are trying to do but here's some code that might help. This will look through all the rows in the range(I used range A1 to A10) and if a row is hidden it will lock it. Sub FindHiddenAndLock() Dim mcell, MyRange As Range Set MyRange = Range("A1:A10") For Each mcell In MyRange If mcell.Rows.Hidden = True Then mcell.Rows.Locked = True End If Next End Sub Sandy ttbbgg wrote: To add further clarification to what I'm looking for the scenario I was thinking is this: 1)If I knew the attribute to a row/column that is hidden, I could use it in the macro, and then LOCK those rows/columns that were hidden. 2) Then, I would Protect the sheet with password and allow selection on only the unlocked rows/columns. Is this fairly simple? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I password protect hidden sheet? | Excel Discussion (Misc queries) | |||
protect / unprotect VBA project by macro | Excel Discussion (Misc queries) | |||
macro to hidden worksheet | Excel Discussion (Misc queries) | |||
Add protect worksheet password in macro | Excel Worksheet Functions | |||
hidden rows/columns | Excel Discussion (Misc queries) |