+ Reply to Thread
Results 1 to 16 of 16

How to protect certain cells so user can not change them.OnlyWant VBA code to make change.

  1. #1
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    How to protect certain cells so user can not change them.OnlyWant VBA code to make change.

    See enclosed file.

    I have this file where if a user makes a change to the cell entries in the first column then values in same row for cols C and D are cleared out. I dont want user to be able to input values into cells in column C and D. I only want VBA code to be able to make changes to cells in cols Cd and D. Not very familiar with protecting, locking cells.........is there anything i need to be concerned about?
    Attached Files Attached Files

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    Protect the cells & worksheet.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    royuk,
    how do you do this? i selected the cells to lock, then went to the home tab then the format section and under this selected the lock option.......but i can still change the cells entries......must not be donig something right.

  4. #4
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    is this what i need to do:
    Select entire sheet. Then "unlock" all cells. Then select only those cells i wish to protect. Then lock those cells. Then protect the sheet such that theuser can ONLY select those cells which are not locked? Is this how to do it?

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    Hi

    Another way, is to use Custom Validation in Columns C & D.

    Something like this. Highlight the area C2:D12 and in custom validation, rules, type =""
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    is there any concern with doing this and then updating entries / cells via VBA code?

  7. #7
    Forum Contributor
    Join Date
    06-16-2011
    Location
    London
    MS-Off Ver
    Excel XP
    Posts
    276

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    In the worksheet activation event, put this code:


    Please Login or Register  to view this content.
    The thing about allowing VBA to change locked cells is you need to tell it the instruction again every time you open the workbook, it kinda forgets. Therefore, if you protect the sheet automatically every time you activate the sheet then it'll always be protected but VBA will be able to make changes.

  8. #8
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    great info thanks! So maybe i dont want to go the protect cells route....maybe the cell validation route might be better.

    swoop99, i did have one question about what you wrote ....first thanks for the info.......i am not 100% sure i understand what you were saying about excel forgetting.....do i have to protect the sheet each time the workbook opens?

    thanks again........this is exactly what i am looking for.......thanks.

  9. #9
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    2,015

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    or
    see file attached
    Attached Files Attached Files
    Regards, John55
    If you have issues with Code I've provided, I appreciate your feedback.
    In the event Code provided resolves your issue, please mark your Thread as SOLVED.
    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

    ...enjoy -funny parrots-

  10. #10
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    john55, how did you do this? do you think this way is better than the validation way......again thanks for the tips this helps out ALOT!

  11. #11
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel 2010
    Posts
    2,015

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    hi welchs,
    it's just an option, I do not say if it's better or not, it's the same thing as you said in post #4 and unprotect/protect in sheet event.
    (click left A1, it selects whole sheet, format cell, protection, unlock. Select col C,D, format cell, protection, lock. Protect sheet)

  12. #12
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    I think you would be better to learn basic Excel before venturing into VBA

  13. #13
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    I think i posted something in a similar post that i should have posted here so...i will post it here now.

    I have been working on my proj now for a little bit when i have time and this is what i have found.
    I first plan to copy the 4 cells from a worksheet into my data. Then create a new sheet, which will copy the event code to this new sheet, then copy over my data (which includes the 4 cols) to this new sheet.

    What i found is that when copying just the 4 cols to my dataset that the validation example allowed me to just copy the cells over and the validation copied with it. The "cell protection" did not........so if i want to use the cell protection method i have to figure out a way in vba to protect the cells i wanted to protect........

    is there a way to protect cells like i want using vba? if not thats ok i will just use the validation example.

  14. #14
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    fotis1991,

    how to manually undo the validation you put in your example file. I cant seem to "turn-it-off" manually.

  15. #15
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    fortis1191, i figured it out. i just cleared all.

    i assume there is no way to password protect the validated cells criteria?

  16. #16
    Forum Contributor
    Join Date
    12-01-2007
    Location
    USA-North Carolina
    MS-Off Ver
    MS Office 2016
    Posts
    2,601

    Re: How to protect certain cells so user can not change them.OnlyWant VBA code to make cha

    Ok, i am still working on this macro which involves protecting certain cells. I decided to "try" the protect cells method instead of the "validating" method. Perhaps i made a mistake but time will tell.

    Anyway, here is the code i went with to protect certain cells.

    Please Login or Register  to view this content.


    The problem i am having is that now i get messages that says sheets are protected. I think someone in this thread was trying to warn me about this but i did not understand at the time.....well i still dont understand thats why i am having problems and cant figure out what to do.


    Any ideas?

    Also, what really confuses me is that i locked/protected "i thought" only certain cells on a given sheet. But the code is acting like i protected the entire sheet. And of course if i unprotect the entire sheet then i "UNDO" the protection of the cells i really want to protect........

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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