Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Can you use a validation formula to lock a cell? If so could you give me an
example of how to write it? Thanks in advance, MIke |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
No you can't use validation to lock a cell
-- Regards, Peo Sjoblom "MIke" wrote in message ... Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
As Peo mentioned..........Formulas cannot lock cells.
DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Gord,
I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Assuming all cells are set to "unlocked" to begin with and sheet is password
protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Gord,
The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Try this version Mike.
Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Gord,
Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
You should be able to incorporate a second range into the same event.
You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Gord,
Is there a way to trigger the locking from a change in fill color? If I can do this it will take care of both events. I have the cells conditionally formatted to turn red if the thresholds are met. Thanks again for all of your help. Mike "Gord Dibben" wrote: You should be able to incorporate a second range into the same event. You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Color change from CF will not trigger the calculate event.
The condition that caused the CF color change will trigger the event. What is the "other range" and what do you need done when that range re-calculates and meets threshholds? Gord On Fri, 2 Nov 2007 05:30:01 -0700, MIke wrote: Gord, Is there a way to trigger the locking from a change in fill color? If I can do this it will take care of both events. I have the cells conditionally formatted to turn red if the thresholds are met. Thanks again for all of your help. Mike "Gord Dibben" wrote: You should be able to incorporate a second range into the same event. You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
The other range is Q7:Q37 and I need the same ranges of cells to lock (B:N).
Thanks, Mike "Gord Dibben" wrote: Color change from CF will not trigger the calculate event. The condition that caused the CF color change will trigger the event. What is the "other range" and what do you need done when that range re-calculates and meets threshholds? Gord On Fri, 2 Nov 2007 05:30:01 -0700, MIke wrote: Gord, Is there a way to trigger the locking from a change in fill color? If I can do this it will take care of both events. I have the cells conditionally formatted to turn red if the thresholds are met. Thanks again for all of your help. Mike "Gord Dibben" wrote: You should be able to incorporate a second range into the same event. You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
That could conflict with O7:O37 threshholds, could it not?
Or do you mean if both O7 and Q7 were 8 then lock B7:N7? Or an if/or condition? Gord On Fri, 2 Nov 2007 07:47:02 -0700, MIke wrote: The other range is Q7:Q37 and I need the same ranges of cells to lock (B:N). Thanks, Mike "Gord Dibben" wrote: Color change from CF will not trigger the calculate event. The condition that caused the CF color change will trigger the event. What is the "other range" and what do you need done when that range re-calculates and meets threshholds? Gord On Fri, 2 Nov 2007 05:30:01 -0700, MIke wrote: Gord, Is there a way to trigger the locking from a change in fill color? If I can do this it will take care of both events. I have the cells conditionally formatted to turn red if the thresholds are met. Thanks again for all of your help. Mike "Gord Dibben" wrote: You should be able to incorporate a second range into the same event. You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Gord,
They are two different thresholds. Is there a way I could do it with macros. Thanks Mike "Gord Dibben" wrote: That could conflict with O7:O37 threshholds, could it not? Or do you mean if both O7 and Q7 were 8 then lock B7:N7? Or an if/or condition? Gord On Fri, 2 Nov 2007 07:47:02 -0700, MIke wrote: The other range is Q7:Q37 and I need the same ranges of cells to lock (B:N). Thanks, Mike "Gord Dibben" wrote: Color change from CF will not trigger the calculate event. The condition that caused the CF color change will trigger the event. What is the "other range" and what do you need done when that range re-calculates and meets threshholds? Gord On Fri, 2 Nov 2007 05:30:01 -0700, MIke wrote: Gord, Is there a way to trigger the locking from a change in fill color? If I can do this it will take care of both events. I have the cells conditionally formatted to turn red if the thresholds are met. Thanks again for all of your help. Mike "Gord Dibben" wrote: You should be able to incorporate a second range into the same event. You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Mike
You're a little tight-fisted with the details. We've so far gotten columns B:N to lock based on a threshhold of 8 in O7:O37 Would Q7:Q37 override that? What's the threshhold in that range? Are you wanting an IF AND condition? Are you wanting an IF OR condition? Gord On Mon, 5 Nov 2007 06:19:01 -0800, MIke wrote: Gord, They are two different thresholds. Is there a way I could do it with macros. Thanks Mike "Gord Dibben" wrote: That could conflict with O7:O37 threshholds, could it not? Or do you mean if both O7 and Q7 were 8 then lock B7:N7? Or an if/or condition? Gord On Fri, 2 Nov 2007 07:47:02 -0700, MIke wrote: The other range is Q7:Q37 and I need the same ranges of cells to lock (B:N). Thanks, Mike "Gord Dibben" wrote: Color change from CF will not trigger the calculate event. The condition that caused the CF color change will trigger the event. What is the "other range" and what do you need done when that range re-calculates and meets threshholds? Gord On Fri, 2 Nov 2007 05:30:01 -0700, MIke wrote: Gord, Is there a way to trigger the locking from a change in fill color? If I can do this it will take care of both events. I have the cells conditionally formatted to turn red if the thresholds are met. Thanks again for all of your help. Mike "Gord Dibben" wrote: You should be able to incorporate a second range into the same event. You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula to lock cell?
Gord,
Sorry. I am not tryiong to be tight fisted but I dont work with this stuff alot. I need column b:n to lock if the value in O7:O37 exceeds 8 Also, I need b:n to lock if the value in Q7:Q37 exceeds a different value "20" I dont need it to overide the original range it is just another condition to locking that group of cells so I think it is an IF OR condition. Let me know if that makes sense to you. Thanks again for all the help. Mike "Gord Dibben" wrote: Mike You're a little tight-fisted with the details. We've so far gotten columns B:N to lock based on a threshhold of 8 in O7:O37 Would Q7:Q37 override that? What's the threshhold in that range? Are you wanting an IF AND condition? Are you wanting an IF OR condition? Gord On Mon, 5 Nov 2007 06:19:01 -0800, MIke wrote: Gord, They are two different thresholds. Is there a way I could do it with macros. Thanks Mike "Gord Dibben" wrote: That could conflict with O7:O37 threshholds, could it not? Or do you mean if both O7 and Q7 were 8 then lock B7:N7? Or an if/or condition? Gord On Fri, 2 Nov 2007 07:47:02 -0700, MIke wrote: The other range is Q7:Q37 and I need the same ranges of cells to lock (B:N). Thanks, Mike "Gord Dibben" wrote: Color change from CF will not trigger the calculate event. The condition that caused the CF color change will trigger the event. What is the "other range" and what do you need done when that range re-calculates and meets threshholds? Gord On Fri, 2 Nov 2007 05:30:01 -0700, MIke wrote: Gord, Is there a way to trigger the locking from a change in fill color? If I can do this it will take care of both events. I have the cells conditionally formatted to turn red if the thresholds are met. Thanks again for all of your help. Mike "Gord Dibben" wrote: You should be able to incorporate a second range into the same event. You can't however, use two events of the same type in one sheet or you would get a conflict. Gord On Thu, 1 Nov 2007 11:58:02 -0700, MIke wrote: Gord, Thanks. It works great. I am new to visual basic and that saved me a lot of time. Can I ask one more question? I have another range on the same worksheet that I need the same thing to happen. Can I use the same Private Sub Woksheet_Calculate or can I only use that once per worksheet? Thanks again for all your help, Mike "Gord Dibben" wrote: Try this version Mike. Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True Else Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 1 Nov 2007 05:24:02 -0700, MIke wrote: Gord, The code you gave me works but it only locks the cell 13 spots from the target cell. Is there a way to lock all 13 cells to the left of the target? Do you have any suggestions? Thanks for your help, Mike "Gord Dibben" wrote: Assuming all cells are set to "unlocked" to begin with and sheet is password protected Private Sub Worksheet_Calculate() Dim myCell As Range On Error GoTo ws_exit: Application.EnableEvents = False ActiveSheet.Unprotect Password:="justme" For Each myCell In Range("O7:O37") If myCell.Value 8 Then myCell.Offset(0, -13).Locked = True End If Next myCell ActiveSheet.Protect Password:="justme" ws_exit: Application.EnableEvents = True End Sub Gord On Thu, 25 Oct 2007 04:55:01 -0700, MIke wrote: Gord, I have a formula in Column O7:O37. I need cells B7:N37 to lock when the value from the formula exceeds a certain threshold. Thanks for the help, Mike "Gord Dibben" wrote: As Peo mentioned..........Formulas cannot lock cells. DV can't lock cells. Conditional formatting can't lock cells. You would be best use VBA event code. If you describe what you need locking and when, someone could supply some code. Gord Dibben MS Excel MVP On Wed, 24 Oct 2007 09:48:08 -0700, MIke wrote: Can you use a validation formula to lock a cell? If so could you give me an example of how to write it? Thanks in advance, MIke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i lock a cell? to save formula or function in it? | Excel Discussion (Misc queries) | |||
I want to copy a formula down several rows and lock a cell ref. | Excel Discussion (Misc queries) | |||
In creating a spreadsheet how can I lock a formula cell only? | New Users to Excel | |||
How do I lock a formula in a cell in an Excel worksheet? | Excel Discussion (Misc queries) | |||
How do I lock a formula in a cell in an Excel worksheet? | Excel Worksheet Functions |