+ Reply to Thread
Results 1 to 3 of 3

Prevent Users from Changing Value of a Cell if Condition is Met

  1. #1
    Forum Contributor
    Join Date
    12-19-2011
    Location
    Central Europe
    MS-Off Ver
    Excel 2016
    Posts
    359

    Prevent Users from Changing Value of a Cell if Condition is Met

    Hi all

    How could I prevent a user from changing a cell of the selected cell, when a certain condition is met in another cell on the same row (without locking the cell)

    Let's say B22 has value "ABC", then the user should not be allowed to make changes to any other cell on row 22

    Thanks
    FD

  2. #2
    Forum Contributor
    Join Date
    06-11-2014
    MS-Off Ver
    Office 2003, 2007 & 2010
    Posts
    119

    Re: Prevent Users from Changing Value of a Cell if Condition is Met

    Hey FallingDown,

    I would use the Worsheet_SelectionChange and Worksheet_Change functions as below:

    Please Login or Register  to view this content.
    Obviously change the "ABC" criteria for your own, and that the column references 2 and "B" are changed for your required column.

    Actual sheet protection would be better though due to the fact that VBA can be broken using Esc or Ctrl+Break (both of which can be ignored in code, but just in case of infinite loops, it's best not to)

    Edit Sorry, should have explained the code really. Basically the SelectionChange function fires whenever the user selects a cell on the sheet where the code is, when this happens, the value in B22 (or whatever row) is checked to see if it meets your "ABC" criteria and, if so, stores the current value of the cell in a public variable. Then, if the user changes that cells value and the criteria in B22 is still "ABC", it will replace what value they type with the original value form the public variable.
    Last edited by AranDG; 10-02-2015 at 03:38 AM.

  3. #3
    Forum Contributor
    Join Date
    12-19-2011
    Location
    Central Europe
    MS-Off Ver
    Excel 2016
    Posts
    359

    Re: Prevent Users from Changing Value of a Cell if Condition is Met

    Thanks Aran, that helped
    I've slightly modified that to

    Please Login or Register  to view this content.

+ 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. Prevent users from changing formatting when pasting in data.
    By Nitefox in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-08-2014, 05:03 PM
  2. Want to prevent users from changing location of shape textboxes
    By korrin.anderson@gmail.com in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-02-2006, 11:10 AM
  3. Prevent users from changing a pivot table
    By Popeye in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 04-08-2006, 09:25 AM
  4. [SOLVED] Prevent users from changing the password
    By roel1973 in forum Excel General
    Replies: 1
    Last Post: 04-05-2006, 08:25 AM
  5. [SOLVED] How to prevent users from changing cell format
    By Tetsuya Oguma in forum Excel General
    Replies: 1
    Last Post: 03-17-2006, 07:10 AM
  6. [SOLVED] Prevent other users from selecting next available auto-populate cell
    By burl_rfc_h in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-07-2006, 11:25 PM
  7. Replies: 4
    Last Post: 03-28-2005, 05:06 PM
  8. Replies: 7
    Last Post: 03-27-2005, 08:06 PM

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