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.
|