Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock Cell if another cell contains data
Say we have worksheets sa and sb. In worksheet code for sa enter:
Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub In a standard module: Sub routine() Dim w As Worksheet Set w = Worksheets("sb") For Each r In w.UsedRange r.Locked = r.HasFormula Next End Sub -- Gary's Student "adodson" wrote: I would like to lock formulas in cells on worksheet B if data is entered in worksheet A. Otherwise, the user would need to enter data on top of the formula. How would I go about this? I tried looking through other posts and couldn't find this... so thanks for any assistance you may offer. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock Cell if another cell contains data
This might be faster:
Sub routine() Dim rng as Range Dim w As Worksheet Set w = Worksheets("sb") 'w.Unprotect On Error Resume Next set rng = w.UsedRange.SpecialCells(xlFormulas) On Error goto 0 if not rng is nothing then rng.Locked = True End if 'w.Protect End Sub that said, I would say the OP needs to provide a better explanation of what he wants, because his explanation doesn't make a whole lot of sense to me. If the formulas are in worksheet B, why would entries in worksheet A "need to enter data on top of the formula." ? -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Say we have worksheets sa and sb. In worksheet code for sa enter: Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub In a standard module: Sub routine() Dim w As Worksheet Set w = Worksheets("sb") For Each r In w.UsedRange r.Locked = r.HasFormula Next End Sub -- Gary's Student "adodson" wrote: I would like to lock formulas in cells on worksheet B if data is entered in worksheet A. Otherwise, the user would need to enter data on top of the formula. How would I go about this? I tried looking through other posts and couldn't find this... so thanks for any assistance you may offer. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock Cell if another cell contains data
If they enter stuff in space A, I want it to flow through to space B.
However, if nothing is entered in space A, I don't want space B to be locked. They have an option of whether they want to utilize space A or provide their own version of space A separate. Thank you for the responses. "Tom Ogilvy" wrote: This might be faster: Sub routine() Dim rng as Range Dim w As Worksheet Set w = Worksheets("sb") 'w.Unprotect On Error Resume Next set rng = w.UsedRange.SpecialCells(xlFormulas) On Error goto 0 if not rng is nothing then rng.Locked = True End if 'w.Protect End Sub that said, I would say the OP needs to provide a better explanation of what he wants, because his explanation doesn't make a whole lot of sense to me. If the formulas are in worksheet B, why would entries in worksheet A "need to enter data on top of the formula." ? -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Say we have worksheets sa and sb. In worksheet code for sa enter: Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub In a standard module: Sub routine() Dim w As Worksheet Set w = Worksheets("sb") For Each r In w.UsedRange r.Locked = r.HasFormula Next End Sub -- Gary's Student "adodson" wrote: I would like to lock formulas in cells on worksheet B if data is entered in worksheet A. Otherwise, the user would need to enter data on top of the formula. How would I go about this? I tried looking through other posts and couldn't find this... so thanks for any assistance you may offer. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock Cell if another cell contains data
If Not intersect(Target,Range("SpaceA")) is nothing then
'Range("spaceB").Parent.UnProtect if application.Counta(range("SpaceA")) 0 then Range("SpaceB").Locked = True else Range("SpaceB").Locked = False End if 'Range("spaceB").Parent.Protect end if -- Regards, Tom Ogilvy "adodson" wrote in message ... If they enter stuff in space A, I want it to flow through to space B. However, if nothing is entered in space A, I don't want space B to be locked. They have an option of whether they want to utilize space A or provide their own version of space A separate. Thank you for the responses. "Tom Ogilvy" wrote: This might be faster: Sub routine() Dim rng as Range Dim w As Worksheet Set w = Worksheets("sb") 'w.Unprotect On Error Resume Next set rng = w.UsedRange.SpecialCells(xlFormulas) On Error goto 0 if not rng is nothing then rng.Locked = True End if 'w.Protect End Sub that said, I would say the OP needs to provide a better explanation of what he wants, because his explanation doesn't make a whole lot of sense to me. If the formulas are in worksheet B, why would entries in worksheet A "need to enter data on top of the formula." ? -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Say we have worksheets sa and sb. In worksheet code for sa enter: Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub In a standard module: Sub routine() Dim w As Worksheet Set w = Worksheets("sb") For Each r In w.UsedRange r.Locked = r.HasFormula Next End Sub -- Gary's Student "adodson" wrote: I would like to lock formulas in cells on worksheet B if data is entered in worksheet A. Otherwise, the user would need to enter data on top of the formula. How would I go about this? I tried looking through other posts and couldn't find this... so thanks for any assistance you may offer. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock Cell if another cell contains data
Remember this:
Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub so: Private Sub Worksheet_Change(ByVal Target As Range) If Not intersect(Target,Range("SpaceA")) is nothing then 'Range("spaceB").Parent.UnProtect if application.Counta(range("SpaceA")) 0 then Range("SpaceB").Locked = True else Range("SpaceB").Locked = False End if 'Range("spaceB").Parent.Protect end if End Sub You see, Space A and Space B mean nothing to me. In my sample code, I assumed they were two named ranges. This code goes in the sheet where you want the code to operate and where SpaceA and SpaceB would be defined. Unfortunately, programming required knowledge and when you don't explain thinks, people have to guess and make assumptions to illustrate an approach. If space A means a worksheet and space B means a worksheet then This would go in the Sheet module of the sheet named SpaceA Private Sub Worksheet_Change(ByVal Target As Range) If Not intersect(Target,Worksheets("SpaceA").Range("A1:A1 0")) is nothing then Worksheets("spaceB").UnProtect if application.Counta(worksheets("SpaceA").Range("A1: A10")) 0 then Worksheets("SpaceB").Range("A1:A10").Locked = True else Worksheets("SpaceB").Range("A1:A10").Locked = False End if Worksheets("spaceB").Protect end if End Sub -- Regards, Tom Ogilvy "adodson" wrote in message ... Ok, I found how to refer to a worksheet/cell in the VB help files, but I can't find anything about target or why it might be causing the error? "adodson" wrote: Forgive my inexperience, but when I put this code at the top of my edit macro area, and replace "space a" and "space b" with the names of the worksheet, I receive a compile error highlighting "Target" Any suggestions as to what I'm doing incorrect? Also, how would I modify the code to check a specific cell or range of cells within "Space A" to block specific cells in "Space B"? Thanks again for your assistance. "Tom Ogilvy" wrote: If Not intersect(Target,Range("SpaceA")) is nothing then 'Range("spaceB").Parent.UnProtect if application.Counta(range("SpaceA")) 0 then Range("SpaceB").Locked = True else Range("SpaceB").Locked = False End if 'Range("spaceB").Parent.Protect end if -- Regards, Tom Ogilvy "adodson" wrote in message ... If they enter stuff in space A, I want it to flow through to space B. However, if nothing is entered in space A, I don't want space B to be locked. They have an option of whether they want to utilize space A or provide their own version of space A separate. Thank you for the responses. "Tom Ogilvy" wrote: This might be faster: Sub routine() Dim rng as Range Dim w As Worksheet Set w = Worksheets("sb") 'w.Unprotect On Error Resume Next set rng = w.UsedRange.SpecialCells(xlFormulas) On Error goto 0 if not rng is nothing then rng.Locked = True End if 'w.Protect End Sub that said, I would say the OP needs to provide a better explanation of what he wants, because his explanation doesn't make a whole lot of sense to me. If the formulas are in worksheet B, why would entries in worksheet A "need to enter data on top of the formula." ? -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Say we have worksheets sa and sb. In worksheet code for sa enter: Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub In a standard module: Sub routine() Dim w As Worksheet Set w = Worksheets("sb") For Each r In w.UsedRange r.Locked = r.HasFormula Next End Sub -- Gary's Student "adodson" wrote: I would like to lock formulas in cells on worksheet B if data is entered in worksheet A. Otherwise, the user would need to enter data on top of the formula. How would I go about this? I tried looking through other posts and couldn't find this... so thanks for any assistance you may offer. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lock Cell if another cell contains data
Thank you for your continued response.
I can see where it can be confusing, sorry. I tend to try to simplify things because 1) I'm not sure on what information is needed since this area is new to me (any beginning VB books you would recommend for working in excel?), and 2) sometimes too much detail seems confusing when discussing an overall graphic-visual-type problem. I will try again tomorrow at work with the new information provided. Thanks. "Tom Ogilvy" wrote: Remember this: Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub so: Private Sub Worksheet_Change(ByVal Target As Range) If Not intersect(Target,Range("SpaceA")) is nothing then 'Range("spaceB").Parent.UnProtect if application.Counta(range("SpaceA")) 0 then Range("SpaceB").Locked = True else Range("SpaceB").Locked = False End if 'Range("spaceB").Parent.Protect end if End Sub You see, Space A and Space B mean nothing to me. In my sample code, I assumed they were two named ranges. This code goes in the sheet where you want the code to operate and where SpaceA and SpaceB would be defined. Unfortunately, programming required knowledge and when you don't explain thinks, people have to guess and make assumptions to illustrate an approach. If space A means a worksheet and space B means a worksheet then This would go in the Sheet module of the sheet named SpaceA Private Sub Worksheet_Change(ByVal Target As Range) If Not intersect(Target,Worksheets("SpaceA").Range("A1:A1 0")) is nothing then Worksheets("spaceB").UnProtect if application.Counta(worksheets("SpaceA").Range("A1: A10")) 0 then Worksheets("SpaceB").Range("A1:A10").Locked = True else Worksheets("SpaceB").Range("A1:A10").Locked = False End if Worksheets("spaceB").Protect end if End Sub -- Regards, Tom Ogilvy "adodson" wrote in message ... Ok, I found how to refer to a worksheet/cell in the VB help files, but I can't find anything about target or why it might be causing the error? "adodson" wrote: Forgive my inexperience, but when I put this code at the top of my edit macro area, and replace "space a" and "space b" with the names of the worksheet, I receive a compile error highlighting "Target" Any suggestions as to what I'm doing incorrect? Also, how would I modify the code to check a specific cell or range of cells within "Space A" to block specific cells in "Space B"? Thanks again for your assistance. "Tom Ogilvy" wrote: If Not intersect(Target,Range("SpaceA")) is nothing then 'Range("spaceB").Parent.UnProtect if application.Counta(range("SpaceA")) 0 then Range("SpaceB").Locked = True else Range("SpaceB").Locked = False End if 'Range("spaceB").Parent.Protect end if -- Regards, Tom Ogilvy "adodson" wrote in message ... If they enter stuff in space A, I want it to flow through to space B. However, if nothing is entered in space A, I don't want space B to be locked. They have an option of whether they want to utilize space A or provide their own version of space A separate. Thank you for the responses. "Tom Ogilvy" wrote: This might be faster: Sub routine() Dim rng as Range Dim w As Worksheet Set w = Worksheets("sb") 'w.Unprotect On Error Resume Next set rng = w.UsedRange.SpecialCells(xlFormulas) On Error goto 0 if not rng is nothing then rng.Locked = True End if 'w.Protect End Sub that said, I would say the OP needs to provide a better explanation of what he wants, because his explanation doesn't make a whole lot of sense to me. If the formulas are in worksheet B, why would entries in worksheet A "need to enter data on top of the formula." ? -- Regards, Tom Ogilvy "Gary''s Student" wrote in message ... Say we have worksheets sa and sb. In worksheet code for sa enter: Private Sub Worksheet_Change(ByVal Target As Range) Call routine End Sub In a standard module: Sub routine() Dim w As Worksheet Set w = Worksheets("sb") For Each r In w.UsedRange r.Locked = r.HasFormula Next End Sub -- Gary's Student "adodson" wrote: I would like to lock formulas in cells on worksheet B if data is entered in worksheet A. Otherwise, the user would need to enter data on top of the formula. How would I go about this? I tried looking through other posts and couldn't find this... so thanks for any assistance you may offer. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock data in a cell a specific cell based on selection on other ce | Excel Worksheet Functions | |||
Lock Data in Cell after entered | Excel Discussion (Misc queries) | |||
Lock Cell After Data Entry | Excel Discussion (Misc queries) | |||
Lock a cell when a given cell contains data | Excel Programming | |||
lock one cell when data in another | Excel Programming |