A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Locking other cells based on values in a cell



 
 
Thread Tools Display Modes
  #1  
Old January 9th, 2004, 05:54 PM
external usenet poster
 
Posts: n/a
Default Locking other cells based on values in a cell



| A| B| C| D|
------------------------------------------
1|Task |Desc |Hrs |Date |
2|Stucco |
3|Paint |
4|Plumb |

Lets say above is my sample spread sheet. How can I
block/lock the cells for col B, Col C and Col D in a row
if the value in Col A of same row is not "Plumb".

In other words if A2 is not equal to "Plumb" then cells
B2, C2 and D2 has to be locked and made not editable or
non-data-enterable.


Please help

Thanks

Thanks
Mario
  #2  
Old January 9th, 2004, 06:54 PM
Dave Smith
external usenet poster
 
Posts: n/a
Default Locking other cells based on values in a cell

This function will lock or unlock cells in columns B-D when the value in A
is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColAChanged As Range
Dim cCell As Range

If Not Intersect(Target, Range("A:A")) Is Nothing Then
ActiveSheet.Unprotect
Set rColAChanged = Intersect(Target, Range("A:A"))
For Each cCell In rColAChanged
cCell.Offset(0, 1).Resize(1, 3).Locked = (cCell.Value
"Plumb")
Next
ActiveSheet.Protect
End If
End Sub

It would be nice if this sort of thing could be done as a conditional
format. Anyone have a better way to do this?

Dave

wrote in message
...


| A| B| C| D|
------------------------------------------
1|Task |Desc |Hrs |Date |
2|Stucco |
3|Paint |
4|Plumb |

Lets say above is my sample spread sheet. How can I
block/lock the cells for col B, Col C and Col D in a row
if the value in Col A of same row is not "Plumb".

In other words if A2 is not equal to "Plumb" then cells
B2, C2 and D2 has to be locked and made not editable or
non-data-enterable.


Please help

Thanks

Thanks
Mario



  #3  
Old January 12th, 2004, 04:19 PM
Mario
external usenet poster
 
Posts: n/a
Default Locking other cells based on values in a cell


I have a question on the protection now. Do I have to
protect the entire worksheet before I can use the above
sub-routine.

I also have another question. How can I selectively
unlock or unprotect certain cells so that users can enter
data only in certain cells., where as the rest of the
work sheet is protected.

one Final question. I have a button linked to a macro.
When the button is clicked the macro does some
calculations using advanced filters in another part of
the worksheet and returns the results to a cell near the
button. If I lock the work sheet, then run the macro (by
clicking the button) Excel throws an error saying the
sheet is protected and has to be unprotected.

Please help
-----Original Message-----
This function will lock or unlock cells in columns B-D

when the value in A
is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColAChanged As Range
Dim cCell As Range

If Not Intersect(Target, Range("A:A")) Is Nothing

Then
ActiveSheet.Unprotect
Set rColAChanged = Intersect(Target, Range

("A:A"))
For Each cCell In rColAChanged
cCell.Offset(0, 1).Resize(1, 3).Locked =

(cCell.Value
"Plumb")
Next
ActiveSheet.Protect
End If
End Sub

It would be nice if this sort of thing could be done as

a conditional
format. Anyone have a better way to do this?

Dave

wrote in message
...


| A| B| C| D|
------------------------------------------
1|Task |Desc |Hrs |Date |
2|Stucco |
3|Paint |
4|Plumb |

Lets say above is my sample spread sheet. How can I
block/lock the cells for col B, Col C and Col D in a

row
if the value in Col A of same row is not "Plumb".

In other words if A2 is not equal to "Plumb" then cells
B2, C2 and D2 has to be locked and made not editable or
non-data-enterable.


Please help

Thanks

Thanks
Mario



.

  #4  
Old January 13th, 2004, 01:53 AM
Dave Smith
external usenet poster
 
Posts: n/a
Default Locking other cells based on values in a cell

The function unprotects the sheet, determines if the selected cell(s)
require locking of BCD and then protects the sheet, so you only need to
protect the sheet if you unprotect it manually. Even in that event, the
first time you make a change it column A, it will protect it automatically.

You might however need to prime the pump (so to speak) by causing the cells
in column A to be changed to trigger the function. To do this, copy column
A and paste it over column A (actually you might just want to copy and paste
the part with values as it could take a while to do the whole column). This
will cause the function to relock the cells in BCD as required.

To unprotect cells, unprotect the sheet, select the cells you wish to
unlock, then reprotect the sheet. However, the function may relock the cell
if it's in B C or D as changes are made. As written there is no way around
this.

The target cell is likely locked. You can unlock it as above but again the
function may relock it.

If the area you need the function to cover includes all of ABCD then you can
just unlock all cells on the sheet, then prime the pump as above. You'll
then need to lock any additional cells you need locked.

If the area you need the function does not include all of ABDC, you can
adjust the fuction by replace the text "A:A" with the range of column A to
monitor (e.g. "A2:A25") in both places it appears in the code. Then unlock
all cells, prime the pump and relock other cells as needed.

A more general solution that occurs to me would be to use a second hidden
sheet as a locking map. You could then place flags on the map to indicate
whether matching cells on the first sheet should be locked. These flags
could be generated by formulas yielding condional locking. The check to
lock a cell would be made as it becomes selected. However, this wouldn't be
efficient for large selections (whole columns say), and I'm not sure if you
really need a solution this general or if there isn't an easier or better
way to accomplish much the same thing.

HTH


"Mario" wrote in message
...

I have a question on the protection now. Do I have to
protect the entire worksheet before I can use the above
sub-routine.

I also have another question. How can I selectively
unlock or unprotect certain cells so that users can enter
data only in certain cells., where as the rest of the
work sheet is protected.

one Final question. I have a button linked to a macro.
When the button is clicked the macro does some
calculations using advanced filters in another part of
the worksheet and returns the results to a cell near the
button. If I lock the work sheet, then run the macro (by
clicking the button) Excel throws an error saying the
sheet is protected and has to be unprotected.

Please help
-----Original Message-----
This function will lock or unlock cells in columns B-D

when the value in A
is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rColAChanged As Range
Dim cCell As Range

If Not Intersect(Target, Range("A:A")) Is Nothing

Then
ActiveSheet.Unprotect
Set rColAChanged = Intersect(Target, Range

("A:A"))
For Each cCell In rColAChanged
cCell.Offset(0, 1).Resize(1, 3).Locked =

(cCell.Value
"Plumb")
Next
ActiveSheet.Protect
End If
End Sub

It would be nice if this sort of thing could be done as

a conditional
format. Anyone have a better way to do this?

Dave

wrote in message
...


| A| B| C| D|
------------------------------------------
1|Task |Desc |Hrs |Date |
2|Stucco |
3|Paint |
4|Plumb |

Lets say above is my sample spread sheet. How can I
block/lock the cells for col B, Col C and Col D in a

row
if the value in Col A of same row is not "Plumb".

In other words if A2 is not equal to "Plumb" then cells
B2, C2 and D2 has to be locked and made not editable or
non-data-enterable.


Please help

Thanks

Thanks
Mario



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:52 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.