Hi,
please help to write VBA code to lock cells based on the other cell value.
In Sheet 1 from column A to F i have some data, based on the column F value i want to block the columns W,X,Y, means wherever the column F value is having "Died" I want to unlock the columns W,X,Y to enter some data, if it is not "Died" I want to lock columns W,X,Y and cursor has to move from column V to column Z
Please help me
Thanks & Regards
Hi
something like this should do the job:
Put this in the sheet module where your data are.
Note:
Please Login or Register to view this content.
1.The cells that will be editable should be unlocked in advance e.g. range A:F
2.cells in W:Y should be Locked unlocked properly in advance, i.e. if you have Died in F2, then W2:Y2 should be set to unlocked
3.After running the macro your sheet will be protected, without password and only cell that are unlocked will be editable and selectable.
2. Prot
If you are pleased with a member's answer then use the Star icon to rate it.
Thank you for your reply,
column A to Column F columns are not editable why because those columns are auto generated from other sheet and user is not allowed to change those values. Please help me to write a code for without changing the values in column A to F
Please help me
Thanks in advance
I don't understand your workflow. can you elaborated a little?
which cells should be editable and which - not. how it should lock/unlock the cells?
sorry for inconvenience,
I will explain clearly, In Sheet1 from column A to F are having some data (this is auto generated by other sheet) and it is locked. Based on the available data in column A to F user have to enter some data in other columns (G to Z) which are empty. but I want to lock columns W,X,Y wherever Column F is having other than "Died".
For example
F2 is having "X" user can enter data in columns from G to Z except W,X,Y (When user entered data in to column V immediately cursor has to move from V to Z)
F3 is having "Died" user can enter data in all columns from G to Z
Thank you so much for your help
Please help meOriginally Posted by chavanalini
try this
Please Login or Register to view this content.
Thank you so much for your reply, but it is not working
Please tell me where i have to copy this code and tell me which columns I have to lock and which columns I have to unlock before run this code
Thank you
put it in a normal module.
it doesn't matter which cells are locked or unlocked. I unlock all cells in columns G:Z, then I lock the cells W:Y for which"value in F column is not "Died".
At the end user should be able to edit cells In G:V and Z for which you don't have Died and G:Z for these where F=Died
Thank you,
I inserted a new module and I pasted the code but its not working, I am attaching my excel file, please help me how to do this
Yellow colour columns has to be locked when the F column is not have "Died"
Thanks
I think now this should work
I assume for all cells Locked property is TRUE, which is the default value. The problem was due to the merged cells in row 1Please Login or Register to view this content.
sorry for troubling you it is not working,
If you dont mind can you put this code in to my excel file and see whether it is working or not, if it is working please forward the same file to me
sorry for troubling you so much
and thank you so much for you timely help
thanks
here it is. run macro LockCells
Thank you so much it is working, one small doubt whenever I will open excel file is it i have to run macro?
you should run macro after you add/update values in A:F
Ok but it is little difficult to user to run macro every time is there any other way to do this
i give up. sorry...
it's either user run macro after data are extracted from the DB, or like my first suggestion which you didn't like, if data on worksheet change.
Thank you so much i will take your first suggestion only
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks