Cell Protecton and Conditional Formating

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

Windows XP, Excel 2003


Cell A14 is a Dropdown containing: blank, cash, check and credit card.

Based on conditional formatting formula I have been able to change both the
foreground and background color of other cells. I would like to be able to
lock and unlock cells based on the same conditional formula. Is this
possible?

Barry
 
Cell locking is not a part of Conditional Formatting unfortunately. You
could use Data Validation to reject all entries based on another cell's
value though.

--
Jim
| Windows XP, Excel 2003
|
|
| Cell A14 is a Dropdown containing: blank, cash, check and credit card.
|
| Based on conditional formatting formula I have been able to change both
the
| foreground and background color of other cells. I would like to be able to
| lock and unlock cells based on the same conditional formula. Is this
| possible?
|
| Barry
|
|
 
Ok I acknoledge the fact that cell locking is not part of Conditional
Formating, Although I wish it were.
So, how about a bit of VB code...
Maybe something that would do the same desired function.

As mentioned the Cell contents of A14 is the control.
IF A14 <> "check" then Lock cells....D14 and G14.

I just don't want the cells to be available if A14 doesn't meet the criteria

Barry
 
Name the ranges with appropriate names, it makes the code easier to read and
avoids problems with hard coded ranges should you insert a row.

A14 as "nameA"
D14 as "nameD"
G14 as "nameG"

Use a worksheet_change event something like (not tested)

Private Sub Worksheet_Change(ByVal Target As Range)

Dim isect As Range

'Action for within the range "nameA"
Set isect = Application.Intersect(Target, Range(D14))
If Not (isect Is Nothing) Then 'Was it the "nameA" cell that changed
If Target.value <> "check"
'Code to lock the required cells and do other tests when "NameA" changes
ActiveSheet.Unprotect
Range("NameD").Locked = True
Range("NameG").Locked = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End if
End if
End Sub
 
I must be missing an Object Library because I get a Compile Error pointing
to :

If Target.value <> "check

as the problem.

I have the following Object Libraries installed

Excel
MSForms
Office
stdole
VBA
VBAProject
 
If Target.value <> "check" Then


Gord Dibben MS Excel MVP

I must be missing an Object Library because I get a Compile Error pointing
to :

If Target.value <> "check

as the problem.

I have the following Object Libraries installed

Excel
MSForms
Office
stdole
VBA
VBAProject
Click to expand...
 
First of all, Thank You to those who have responded. You are very kind.

Shaking my head.... I don't understand. Now I'm getting another error.

Here it is:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

It Points to this line in the code

Set isect = Application.Intersect(Target, Range(D14))

Maybe I could post this workbook somewhere so one could look at it and
comment.
Any Ideas?
 
You need quotes around cell addresses:

Range("D14")

--
Jim
| First of all, Thank You to those who have responded. You are very kind.
|
| Shaking my head.... I don't understand. Now I'm getting another error.
|
| Here it is:
| Run-time error '1004':
| Method 'Range' of object '_Worksheet' failed
|
| It Points to this line in the code
|
| Set isect = Application.Intersect(Target, Range(D14))
|
| Maybe I could post this workbook somewhere so one could look at it and
| comment.
| Any Ideas?
|
| "Gord Dibben" <gorddibbATshawDOTca> wrote in message
| | > If Target.value <> "check" Then
| >
| >
| > Gord Dibben MS Excel MVP
| >
| > On Thu, 25 Sep 2008 08:11:05 -0500, "Barry" <[email protected]>
| > wrote:
| >
| >>I must be missing an Object Library because I get a Compile Error
pointing
| >>to :
| >>
| >> If Target.value <> "check
| >>
| >>as the problem.
| >>
| >>I have the following Object Libraries installed
| >>
| >>Excel
| >>MSForms
| >>Office
| >>stdole
| >>VBA
| >>VBAProject
| >>
| >>| >>> Name the ranges with appropriate names, it makes the code easier to
read
| >>> and
| >>> avoids problems with hard coded ranges should you insert a row.
| >>>
| >>> A14 as "nameA"
| >>> D14 as "nameD"
| >>> G14 as "nameG"
| >>>
| >>> Use a worksheet_change event something like (not tested)
| >>>
| >>> Private Sub Worksheet_Change(ByVal Target As Range)
| >>>
| >>> Dim isect As Range
| >>>
| >>> 'Action for within the range "nameA"
| >>> Set isect = Application.Intersect(Target, Range(D14))
| >>> If Not (isect Is Nothing) Then 'Was it the "nameA" cell that changed
| >>> If Target.value <> "check"
| >>> 'Code to lock the required cells and do other tests when "NameA"
| >>> changes
| >>> ActiveSheet.Unprotect
| >>> Range("NameD").Locked = True
| >>> Range("NameG").Locked = True
| >>> ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
| >>> Scenarios:=True
| >>> End if
| >>> End if
| >>> End Sub
| >>>
| >>> On Wed, 24 Sep 2008 11:23:54 -0500, "Barry" <[email protected]>
| >>> wrote:
| >>>
| >>>>Ok I acknoledge the fact that cell locking is not part of Conditional
| >>>>Formating, Although I wish it were.
| >>>>So, how about a bit of VB code...
| >>>>Maybe something that would do the same desired function.
| >>>>
| >>>>As mentioned the Cell contents of A14 is the control.
| >>>>IF A14 <> "check" then Lock cells....D14 and G14.
| >>>>
| >>>>I just don't want the cells to be available if A14 doesn't meet the
| >>>>criteria
| >>>>
| >>>>Barry
| >>>>
| >>>>| >>>>> Cell locking is not a part of Conditional Formatting unfortunately.
| >>>>> You
| >>>>> could use Data Validation to reject all entries based on another
| >>>>> cell's
| >>>>> value though.
| >>>>>
| >>>>> --
| >>>>> Jim
| >>>>> | >>>>> | Windows XP, Excel 2003
| >>>>> |
| >>>>> |
| >>>>> | Cell A14 is a Dropdown containing: blank, cash, check and credit
| >>>>> card.
| >>>>> |
| >>>>> | Based on conditional formatting formula I have been able to change
| >>>>> both
| >>>>> the
| >>>>> | foreground and background color of other cells. I would like to be
| >>>>> able
| >>>>> to
| >>>>> | lock and unlock cells based on the same conditional formula. Is
this
| >>>>> | possible?
| >>>>> |
| >>>>> | Barry
| >>>>> |
| >>>>> |
| >>>>>
| >>>>
| >>> --
| >>> Dave Mills
| >>> There are 10 types of people, those that understand binary and those
| >>> that
| >>> don't.
| >>
| >
|
|
 
I'm getting frusturated...

Now I get another error:

Unable to set the Locked property of the Range class

Points to.

Range("NameD").Locked = True

Maybe I need to walk away for a bit on this. It seems it should be so simple
to do this stuff.
 
First of all, Thank You to those who have responded. You are very kind.

Shaking my head.... I don't understand. Now I'm getting another error.

Here it is:
Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

It Points to this line in the code

Set isect = Application.Intersect(Target, Range(D14))
Click to expand...

As well as Jim's comment on the missing quotes. Be aware that if you do code
"D14" and later insert a row at say row 2 for additional titles or anything at
all the VB code will still say "D14" which will be one cell before the original
target cell. Excel does not modify the target cells in any VB code like it does
in formulae. Hence my suggestion to use named ranges.

Additionally I would use meaningful names like "OpeningPrice", "ClosingPrice",
"Bid", "Offer" not "NameA". You have not said what the values represent so I
used "Name"
 
Back
Top