+ Reply to Thread
Results 1 to 7 of 7

VBA - Locking a range of cells for each row based on a cell in the same row

  1. #1
    Registered User
    Join Date
    01-29-2017
    Location
    Wellington, NZ
    MS-Off Ver
    2016
    Posts
    4

    VBA - Locking a range of cells for each row based on a cell in the same row

    I'd like to lock a range of cells (N:W) if the value in cell C of the same row is "Charter" (and this value is chosen from a drop-down list). I'd like to write the code so that this happens for every row.

    For example, if C10 = "Charter", N10:W10 would be locked, and if C14 = "Charter", N14:W14 would be locked.
    However, if the value in C equals anything other than "Charter", the cells N:W of the corresponding row would be unlocked.

    I'd also like to change the default message with a custom message, but I can't figure out how to focus the message on the would be locked cells.

    I've attached the code below. Any guidance would be much appreciated.

    Please Login or Register  to view this content.

  2. #2
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA - Locking a range of cells for each row based on a cell in the same row

    Hi NathanA
    I don't follow your point about the message
    Try this code:

    Please Login or Register  to view this content.
    Barriers are there for those who don't want to dream

  3. #3
    Registered User
    Join Date
    01-29-2017
    Location
    Wellington, NZ
    MS-Off Ver
    2016
    Posts
    4

    Re: VBA - Locking a range of cells for each row based on a cell in the same row

    Thanks for looking at this.

    I pasted the code, however, none of the cells in N:W were locked even if 'Charter (lite)' was in the same row. I could still click on the cells and type in them.
    If it makes a difference, 'Charter (lite)' is selected from a dropdown list rather than written in as a value.

    Also, what does the line "If Intersect(Target, Range("E:G")) Is Nothing Then" do as E:G is not something the code focuses on?

  4. #4
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA - Locking a range of cells for each row based on a cell in the same row

    Hi @ NathanA

    I'm sorry, I was testing on column E->G instead of N->W, just to have them closer...my mistake. So change that line to:

    Please Login or Register  to view this content.
    Now, whenever you change the value of any cell in columns N through W, the code will be triggered.
    Remember that you need an "exact match" for the text in C(i): "Charter (lite)" to lock the cells in that row(i).

    Perhaps you can be interested in a small variation, and trigger the code whenever the text in C(i) STARTS with the text "Charter", then you can change the line:

    Please Login or Register  to view this content.
    to this:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    01-29-2017
    Location
    Wellington, NZ
    MS-Off Ver
    2016
    Posts
    4

    Re: VBA - Locking a range of cells for each row based on a cell in the same row

    Thanks for your reply. Say cell C2 contains "Charter (lite)" and I click on N2, the code works and the error message comes up. However, after clicking Ok, the whole sheet (every cell) becomes protected and the sheet needs to be unprotected in the Info menu. Is there a way to stop that from happening?

  6. #6
    Valued Forum Contributor
    Join Date
    08-07-2014
    Location
    Quito, Ecuador
    MS-Off Ver
    Excel 2016 & 365, Windows 10
    Posts
    511

    Re: VBA - Locking a range of cells for each row based on a cell in the same row

    Hi @NathanA
    Quite a long time with no answers...eh?

    When you start a new sheet, all the cells are as default "locked", so if you protect the sheet, you cannot write in any cell.
    I've added a new line to unlock all the cells in the active range before doing the work
    In the code, I used the word "charter" to compare and trigger the action..... you must change this word to the right expression.
    Then all the rows that have "charter" in column C will be locked between columns N to W
    please try this new code:


    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    01-29-2017
    Location
    Wellington, NZ
    MS-Off Ver
    2016
    Posts
    4

    Re: VBA - Locking a range of cells for each row based on a cell in the same row

    Perfect, thanks so much!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 05-27-2015, 06:06 AM
  2. Locking and unlocking a range of cells based on a fomula cell value change.
    By tyeinse in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-07-2014, 07:35 PM
  3. Assistance Needed: Locking a Range based on a Cell Value
    By OptimusNaas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2013, 02:33 PM
  4. [SOLVED] Desperate Help needed with Locking out a range of cells based on selection from 1 cell
    By Vcummi in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-31-2012, 04:12 PM
  5. cell locking for range of cells
    By sunshine076 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-11-2010, 11:34 AM
  6. Locking cells based on other cell value
    By smalls in forum Excel General
    Replies: 5
    Last Post: 08-20-2010, 07:30 PM
  7. Locking a Range of Cells Based on Other Cell Values
    By _Ryan_ in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-02-2010, 06:59 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1