Allow VBA code to change a locked cell??

Discussion in 'Microsoft Excel Programming' started by Paul Kraemer, Feb 4, 2010.

  1. Paul Kraemer

    Paul Kraemer Guest

    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
     
    Paul Kraemer, Feb 4, 2010
    #1
    1. Advertisements

  2. 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
     
    Barb Reinhardt, Feb 4, 2010
    #2
    1. Advertisements

  3. Paul Kraemer

    Paul Guest

    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
     
    Paul, Feb 5, 2010
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Chip Pearson

    Re: VBA code to delete VBA code in another Workbook

    Chip Pearson, Sep 15, 2003, in forum: Microsoft Excel Programming
    Replies:
    0
    Views:
    384
    Chip Pearson
    Sep 15, 2003
  2. KimberlyC
    Replies:
    1
    Views:
    258
    Tom Ogilvy
    Jul 8, 2005
  3. CoolCyber

    Not allow user to select a locked cell

    CoolCyber, Nov 13, 2005, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    198
    Guest
    Nov 14, 2005
  4. CAPTGNVR
    Replies:
    2
    Views:
    833
    Gary Keramidas
    Jul 8, 2007
  5. RyanH
    Replies:
    8
    Views:
    197
    Rick Rothstein \(MVP - VB\)
    May 20, 2008
Loading...

Share This Page