Lock a row, then un-lock individual cells?

Posted on 2012-09-19
Last Modified: 2012-09-24
Hello - I've got a situation where I am reading rows from a recordset and putting the field values into cells on a worksheet.

These rows represent budget allocations; if the status of a given allocation is "Active", then the user can change certain fields on the row; otherwise; they can't change anything; it's just there for reference.

So I have this code:

(after the code to insert a row, and set it as a range called "rngInsert")

With rngInsert

      '-- transfer the values from the recordset fields into the worksheet cells
      .Cells(1, 1).Value = rst![ProjactName]
      .Cells(1, 2).Value = rst![ProjectNumber]
      .Cells(1, 3).Value = rst![StatusDesc]
      .Cells(1, 4).Value = rst![Qty]
      .Cells(1, 5).Value = rst![CapAmt]
      .Cells(1, 6).Value = rst![ExpAmt]

        '-- lock the entire row, but if the allocation is still active, open up the amount fields
      .Locked = True
      If rst![StatusDesc] <> "Closed" Then
          .Cells(1, 4).Locked = False
          .Cells(1, 5).Locked = False
          .Cells(1, 6).Locked = False
      End If

End With

It's not working; all the cells are still locked for rows where the status is active. I've  stepped through it in the debugger; the ".Locked = False" lines are getting executed when they should.

Am I leaving something out? (I *am* remembering to protect the worksheet after this routine runs)

Question by:mlagrange
    LVL 17

    Accepted Solution

    Your code fragment seems OK.  All I can think is that at the time when it is run, rngInsert maybe doesn;t refer to what you expect it to.

    I would test it by adding an explicit move to each cell when you set its LOCKED status - then you can step through to see whether it is going to the cell you expect.

    something like:

    If rst![StatusDesc] <> "Closed" Then
              .Cells(1, 4).Locked = False

          End If

    Author Comment

    Thanks for your response

    What is the .Select doing? If it's to make sure a specific cell is actually being affected, should it be before the .Locked ?
    LVL 17

    Assisted Solution

    It's just a way to make it more visible for you to see exactly which cells are being worked on. As you step through the code, it will move the cursor to the cell that was just updated so that you can see which cell the system is amending - and make sure it is the one you intend. It doesn't really matter whether you put it before or after the line that locks the cell.

    As I said, your original code seems OK to me, so this is just about experimenting a bit to try to find out what is wrong.

    Also another thing worth checking: do you have any error trapping ( such as an On Error Resume Next statement ) ? If so, turn it off while testing. Maybe some of these statements are causing errors but you are not seeing them because the errors are being handled and ignored.  ( e.g. if your sheet is still protected and you try to change the LOCKED status of a cell, that would cause an error)

    Author Closing Comment

    Well, Shazaam! I was not... where I thought I was...
    And your 2nd suggestion was dead-on, also (that's why I split the points; 2 great solutions, and you provided both)

    Thanks very much

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now