[Last Call] Learn how to a build a cloud-first strategyRegister Now


Lock a row, then un-lock individual cells?

Posted on 2012-09-19
Medium Priority
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
  • 2
  • 2
LVL 17

Accepted Solution

aflockhart earned 2000 total points
ID: 38416857
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

ID: 38429142
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

aflockhart earned 2000 total points
ID: 38429212
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

ID: 38429706
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

831 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