PC Review


Reply
Thread Tools Rate Thread

Allow VBA code to change a locked cell??

 
 
Paul Kraemer
Guest
Posts: n/a
 
      4th Feb 2010
Hi,

I am using Excel 2007. I have written some VBA code that sets the values in
certain cells using code like the following:

Worksheets("MySheet").Range("A5") = MyValue

This code works when my worksheet is not protected and cell A5 is not
locked. When I protect the worksheet (with A5 locked), my code does not
work. I get run-time error '1004' - "The cell or chart that you are trying
to change is protected and therefore read-only".

I'd like to keep the cell locked (so users cannot edit it), but I do want my
VBA code to be able change the value. Do I have to temporarily unlock the
cell (using VBA) before my code attempts to make a change and then relock the
cell it when my code is done? Or is there an easier way to do this?

Thanks in advance,
Paul
--
Paul Kraemer
 
Reply With Quote
 
 
 
 
Barb Reinhardt
Guest
Posts: n/a
 
      4th Feb 2010
You've got it.
--
HTH,

Barb Reinhardt



"Paul Kraemer" wrote:

> Hi,
>
> I am using Excel 2007. I have written some VBA code that sets the values in
> certain cells using code like the following:
>
> Worksheets("MySheet").Range("A5") = MyValue
>
> This code works when my worksheet is not protected and cell A5 is not
> locked. When I protect the worksheet (with A5 locked), my code does not
> work. I get run-time error '1004' - "The cell or chart that you are trying
> to change is protected and therefore read-only".
>
> I'd like to keep the cell locked (so users cannot edit it), but I do want my
> VBA code to be able change the value. Do I have to temporarily unlock the
> cell (using VBA) before my code attempts to make a change and then relock the
> cell it when my code is done? Or is there an easier way to do this?
>
> Thanks in advance,
> Paul
> --
> Paul Kraemer

 
Reply With Quote
 
 
 
 
Paul
Guest
Posts: n/a
 
      5th Feb 2010
I think you'll have to unprotect the sheet prior to the change and reprotect,
rather than unlocking the cell and locking it again.

--
If the post is helpful, please consider donating something to an animal
charity on my behalf.


"Barb Reinhardt" wrote:

> You've got it.
> --
> HTH,
>
> Barb Reinhardt
>
>
>
> "Paul Kraemer" wrote:
>
> > Hi,
> >
> > I am using Excel 2007. I have written some VBA code that sets the values in
> > certain cells using code like the following:
> >
> > Worksheets("MySheet").Range("A5") = MyValue
> >
> > This code works when my worksheet is not protected and cell A5 is not
> > locked. When I protect the worksheet (with A5 locked), my code does not
> > work. I get run-time error '1004' - "The cell or chart that you are trying
> > to change is protected and therefore read-only".
> >
> > I'd like to keep the cell locked (so users cannot edit it), but I do want my
> > VBA code to be able change the value. Do I have to temporarily unlock the
> > cell (using VBA) before my code attempts to make a change and then relock the
> > cell it when my code is done? Or is there an easier way to do this?
> >
> > Thanks in advance,
> > Paul
> > --
> > Paul Kraemer

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I change a diagram that seems locked (document not locked) =?Utf-8?B?RG9yb3RoeQ==?= Microsoft Word Document Management 1 30th Jun 2005 04:00 PM
Import VBA Code in Excel-File ? (Export VBA Code to file) Matthias Pospiech Microsoft Excel Programming 2 22nd Mar 2005 05:56 PM
VBA code to say Yes or No if any VBA code is present in ActiveWork =?Utf-8?B?RGVubmlz?= Microsoft Excel Misc 2 12th Nov 2004 11:35 PM
VBA code delete code but ask for password and unlock VBA protection WashoeJeff Microsoft Excel Programming 0 27th Jan 2004 08:07 AM
Re: VBA code to delete VBA code in another Workbook Chip Pearson Microsoft Excel Programming 0 15th Sep 2003 03:54 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 12:06 AM.