+ Reply to Thread
Results 1 to 8 of 8

VBA to lock cells based on selection from drop down list

  1. #1
    Registered User
    Join Date
    09-10-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    7

    Smile VBA to lock cells based on selection from drop down list

    Hi,

    I have attached a format here that I want my suppliers to fill in while submitting their price quotations. In the format, column C (Quoted Currency) is a drop down list for selecting the currency in which a particular item is being offered. I have used conditional formatting to turn certain cells blue in the row based on the selected currency. I need help to lock all the other cells in a particular row except for the cells in blue (supplier should be blocked from accidentally entering any value except for in the blue cells).

    Thanks in advance.
    Attached Files Attached Files
    Last edited by RAHUL_MOHAN; 03-31-2017 at 05:51 AM. Reason: No response or views

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to lock cells based on selection from drop down list

    Hi RAHUL

    What range must be checked for lock code in this part of your message.
    help to lock all the other cells
    Is it columns H to X
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    09-10-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA to lock cells based on selection from drop down list

    Hi Sintek,

    Thanks a ton for taking a look at the problem.

    I will try to explain my requirement in the best possible way I can. I want the person filling up the format to have access only to the blue cells.

    The columns A to G and rows 1 to 3 has to be locked at all the times except for the drop down list. The same goes with the columns J, K, L, N, Q, S, V, W & X. The columns T & U will be editable all the time.

    I will give you the other two scenarios for the rest of the cells in the active area:

    1) When C4 is INR

    Columns A to G - locked
    Rows 1 to 3 - locked
    Columns J, K, L, N, Q, S, V, W & X - locked
    Columns H, I, M - locked


    2) When C4 is USD, GBP, EURO

    Columns A to G - locked
    Rows 1 to 3 - locked
    Columns J, K, L, N, Q, S, V, W & X - locked
    Columns O, P, R - locked

    I hope this was of help to you in figuring out the solution.

    Thanks again for your interest.

    Regards

    Rahul

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to lock cells based on selection from drop down list

    Hi RAHUL

    Perhaps this can be a start. Your current color index is 37...That's the light-ish blue shade
    So when something is done on the sheet, this code should theoretically lock all cells within Range H to X that is not light-ish blue.
    Please Login or Register  to view this content.
    Last edited by sintek; 04-01-2017 at 02:27 AM.

  5. #5
    Registered User
    Join Date
    09-10-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA to lock cells based on selection from drop down list

    Hi Sintek,

    Thanks a lot for the inputs. I will try with this code and get back to you.

    Rahul

  6. #6
    Forum Contributor
    Join Date
    10-19-2012
    Location
    chennai
    MS-Off Ver
    Excel 2013
    Posts
    134

    Re: VBA to lock cells based on selection from drop down list

    Please Login or Register  to view this content.
    RoyalRajan

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: VBA to lock cells based on selection from drop down list

    Rahul

    Try this code
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-10-2013
    Location
    Delhi
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: VBA to lock cells based on selection from drop down list

    Hi RoyalRajan,

    I have used the code provided by you but I cant get it to work. I have unlocked all the cells and then pasted your code. Is there anything specific other than this I have to follow.

    Thanks and regards,

    Rahul

+ 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: 1
    Last Post: 10-18-2016, 03:25 AM
  2. Copy paste range of cells based on drop down list selection
    By jrx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-20-2016, 10:23 AM
  3. [SOLVED] Auto-populate cells based on drop down list selection
    By sh1483 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-01-2016, 11:59 PM
  4. Updating cells based on selection from drop down list data validation
    By excelstun in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-18-2016, 05:11 PM
  5. [SOLVED] Lock cells based on response from another - give drop down list
    By DHFE in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-10-2014, 05:39 AM
  6. [SOLVED] Updating cells based on selection from drop down list data validation
    By jingles9 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-24-2013, 02:57 PM
  7. Replies: 1
    Last Post: 04-12-2013, 06:27 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