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. |
|
|
Thread Tools | Display Modes |
#21
|
|||
|
|||
lock cell based on a condition
PUT NOTHING in personal.xls. Remove it if you put it there (Both parts).
Use only the application workbook. -- Gary''s Student - gsnu200733 "Amanda" wrote: I have the first set_up unlock under personal.xls module.....then I placed the second portion you just sent by right clicking on the tab and pasting it, which placed it When I try to type something in the "locked" cell, I get "user-defined type not defined" I also get this on any other excel spreadsheet I open, that I don't want this on. "Gary''s Student" wrote: Remember that the change macro goes in a "special place", right from the normal Excel window: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in: making sure there is only one copy of the code in that window 4. close the VBE window -- Gary''s Student - gsnu200733 "Amanda" wrote: Not sure what I am doing wrong, but when I go under tools, VBA, and paste the below....if I try to run it, worksheet_change does not show up. "Gary''s Student" wrote: The setup reoutine is the same. Here is the new worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Left(Target.Value, 1) = "E" Then Target.Offset(0, 7).Locked = True Target.Offset(0, 10).Locked = False Else Target.Offset(0, 7).Locked = False Target.Offset(0, 10).Locked = True End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub The routine looks for entries in column A. If the entry starts with "E" then H is locked and K is unlocked. If it does not start with W, visa-versa -- Gary''s Student - gsnu200733 "Amanda" wrote: Yes that is fine.... Again, I appreciate all your help! "Gary''s Student" wrote: It CAN be done. Is it O.K. to check the first two characters in column A instead of the "x" in column B to make the decision?? -- Gary''s Student - gsnu200732 "Amanda" wrote: Was it determined that this can't be done? If so, thanks for all your help and attempts! You guys are awesome! "Amanda" wrote: If you know another way...I'm all ears.... Let me explain the form a little more..... There are two types of #'s that will be input into column A: 07-XXX or E-XXX (X being a number)....If the job begins with 07-XXX then I need column H open for filling and column K locked.....if the job begins with E-XXX then I need Column H blocked and column K open I used the formula to give me an easy indicator for my conditional formula to turn the columns that should be blocked dark gray. Does that make sense? "Gary''s Student" wrote: Do we need the Calculate event?? -- Gary''s Student - gsnu200731 "Gord Dibben" wrote: Note that Amanda wants the trigger to be a calculated value of "x" Operative word being "calculated" from formulas. Change_Event won't do that. Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student wrote: I am pretty sure the problem is text wrapping in the post. For example: AlllowFormattingRows is really one line with the one below it. If you are still having trouble, tomorrow I will re-post a much more "copy/paste"-able version. |
#22
|
|||
|
|||
lock cell based on a condition
When I look at the Visual Basic...this is what is showing...
Under my VBA Project (Recruitment Log.xls) Microsoft Excel Object (folder) Sheet 1(log) This Workbook Sheet 1's code is: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Left(Target.Value, 1) = "E" Then Target.Offset(0, 7).Locked = True Target.Offset(0, 10).Locked = False Else Target.Offset(0, 7).Locked = False Target.Offset(0, 10).Locked = True End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub This worksheet's code is: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("B:B"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Target.Value = "x" Then Target.Offset(0, 6).Locked = True Else Target.Offset(0, 6).Locked = False End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub "Gary''s Student" wrote: PUT NOTHING in personal.xls. Remove it if you put it there (Both parts). Use only the application workbook. -- Gary''s Student - gsnu200733 "Amanda" wrote: I have the first set_up unlock under personal.xls module.....then I placed the second portion you just sent by right clicking on the tab and pasting it, which placed it When I try to type something in the "locked" cell, I get "user-defined type not defined" I also get this on any other excel spreadsheet I open, that I don't want this on. "Gary''s Student" wrote: Remember that the change macro goes in a "special place", right from the normal Excel window: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in: making sure there is only one copy of the code in that window 4. close the VBE window -- Gary''s Student - gsnu200733 "Amanda" wrote: Not sure what I am doing wrong, but when I go under tools, VBA, and paste the below....if I try to run it, worksheet_change does not show up. "Gary''s Student" wrote: The setup reoutine is the same. Here is the new worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Left(Target.Value, 1) = "E" Then Target.Offset(0, 7).Locked = True Target.Offset(0, 10).Locked = False Else Target.Offset(0, 7).Locked = False Target.Offset(0, 10).Locked = True End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub The routine looks for entries in column A. If the entry starts with "E" then H is locked and K is unlocked. If it does not start with W, visa-versa -- Gary''s Student - gsnu200733 "Amanda" wrote: Yes that is fine.... Again, I appreciate all your help! "Gary''s Student" wrote: It CAN be done. Is it O.K. to check the first two characters in column A instead of the "x" in column B to make the decision?? -- Gary''s Student - gsnu200732 "Amanda" wrote: Was it determined that this can't be done? If so, thanks for all your help and attempts! You guys are awesome! "Amanda" wrote: If you know another way...I'm all ears.... Let me explain the form a little more..... There are two types of #'s that will be input into column A: 07-XXX or E-XXX (X being a number)....If the job begins with 07-XXX then I need column H open for filling and column K locked.....if the job begins with E-XXX then I need Column H blocked and column K open I used the formula to give me an easy indicator for my conditional formula to turn the columns that should be blocked dark gray. Does that make sense? "Gary''s Student" wrote: Do we need the Calculate event?? -- Gary''s Student - gsnu200731 "Gord Dibben" wrote: Note that Amanda wants the trigger to be a calculated value of "x" Operative word being "calculated" from formulas. Change_Event won't do that. Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student wrote: I am pretty sure the problem is text wrapping in the post. For example: AlllowFormattingRows is really one line with the one below it. If you are still having trouble, tomorrow I will re-post a much more "copy/paste"-able version. |
#23
|
|||
|
|||
lock cell based on a condition
I see the problem.
1. remove the code from the ThisWorkbook area 2. remove the code from the worksheet area 3. leave the code in the standard module (set_up) 4. go back to the worksheet area and paste in our latest version: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Left(Target.Value, 1) = "E" Then Target.Offset(0, 7).Locked = True Target.Offset(0, 10).Locked = False Else Target.Offset(0, 7).Locked = False Target.Offset(0, 10).Locked = True End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub Run setup before entering data in column A -- Gary''s Student - gsnu200733 "Amanda" wrote: When I look at the Visual Basic...this is what is showing... Under my VBA Project (Recruitment Log.xls) Microsoft Excel Object (folder) Sheet 1(log) This Workbook Sheet 1's code is: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Left(Target.Value, 1) = "E" Then Target.Offset(0, 7).Locked = True Target.Offset(0, 10).Locked = False Else Target.Offset(0, 7).Locked = False Target.Offset(0, 10).Locked = True End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub This worksheet's code is: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("B:B"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Target.Value = "x" Then Target.Offset(0, 6).Locked = True Else Target.Offset(0, 6).Locked = False End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub "Gary''s Student" wrote: PUT NOTHING in personal.xls. Remove it if you put it there (Both parts). Use only the application workbook. -- Gary''s Student - gsnu200733 "Amanda" wrote: I have the first set_up unlock under personal.xls module.....then I placed the second portion you just sent by right clicking on the tab and pasting it, which placed it When I try to type something in the "locked" cell, I get "user-defined type not defined" I also get this on any other excel spreadsheet I open, that I don't want this on. "Gary''s Student" wrote: Remember that the change macro goes in a "special place", right from the normal Excel window: 1. right-click the tab name near the bottom of the window 2. select View Code - this brings up a VBE window 3. paste the stuff in: making sure there is only one copy of the code in that window 4. close the VBE window -- Gary''s Student - gsnu200733 "Amanda" wrote: Not sure what I am doing wrong, but when I go under tools, VBA, and paste the below....if I try to run it, worksheet_change does not show up. "Gary''s Student" wrote: The setup reoutine is the same. Here is the new worksheet code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Range("A:A"), Target) Is Nothing Then Exit Sub End If ActiveSheet.Unprotect If Left(Target.Value, 1) = "E" Then Target.Offset(0, 7).Locked = True Target.Offset(0, 10).Locked = False Else Target.Offset(0, 7).Locked = False Target.Offset(0, 10).Locked = True End If ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True ActiveSheet.EnableSelection = xlUnlockedCells End Sub The routine looks for entries in column A. If the entry starts with "E" then H is locked and K is unlocked. If it does not start with W, visa-versa -- Gary''s Student - gsnu200733 "Amanda" wrote: Yes that is fine.... Again, I appreciate all your help! "Gary''s Student" wrote: It CAN be done. Is it O.K. to check the first two characters in column A instead of the "x" in column B to make the decision?? -- Gary''s Student - gsnu200732 "Amanda" wrote: Was it determined that this can't be done? If so, thanks for all your help and attempts! You guys are awesome! "Amanda" wrote: If you know another way...I'm all ears.... Let me explain the form a little more..... There are two types of #'s that will be input into column A: 07-XXX or E-XXX (X being a number)....If the job begins with 07-XXX then I need column H open for filling and column K locked.....if the job begins with E-XXX then I need Column H blocked and column K open I used the formula to give me an easy indicator for my conditional formula to turn the columns that should be blocked dark gray. Does that make sense? "Gary''s Student" wrote: Do we need the Calculate event?? -- Gary''s Student - gsnu200731 "Gord Dibben" wrote: Note that Amanda wants the trigger to be a calculated value of "x" Operative word being "calculated" from formulas. Change_Event won't do that. Gord Dibben MS Excel MVP On Tue, 19 Jun 2007 12:01:01 -0700, Gary''s Student wrote: I am pretty sure the problem is text wrapping in the post. For example: AlllowFormattingRows is really one line with the one below it. If you are still having trouble, tomorrow I will re-post a much more "copy/paste"-able version. |
Thread Tools | |
Display Modes | |
|
|