LinkBack URL
About LinkBacks
Is there a way to create a simple macro where if column B contains "client", then it blocks the user from entering data in columns H and I for that respective row? This would be applied to a table that expands daily as new data is added.
Is there a way to create a simple macro where if column B contains "client", then it blocks the user from entering data in columns H and I for that respective row? This would be applied to a table that expands daily as new data is added.
Right click on sheet name, view code, paste this code and save it. It should lock the cells based on the input. Untested.
Please Login or Register to view this content.
Click the * to say thanks.
Another option:
Start by unlocking all the cells in the sheet. Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Replace the password (in red) with a password of your choosing. Close the code window to return to your sheet. Make an entry in column B and press the RETURN key.
Please Login or Register to view this content.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
I tried both options and neither option worked. I changed "client" to my client's name, and I changed the column to M and N which apply to my worksheet. However, I noticed two things:
1.
Case Is = "client"
Me.Range(Me.Cells(c.Row, "H"), Me.Cells(c.Row, "I")).Locked = True
Me.Range(Me.Cells(c.Row, "I"), Me.Cells(c.Row, "I")).Locked = True
Case Else
Me.Range(Me.Cells(c.Row, "H"), Me.Cells(c.Row, "I")).Locked = False
Me.Range(Me.Cells(c.Row, "I"), Me.Cells(c.Row, "I")).Locked = False
Why is" H" once and "I" thrice used? Wouldn't "H" and "I" be used twice in the code? Also, can the code not allow a person to select the column, just skip over to column J in the respective row?
When I tested it, I tried to enter something in the columns and I receive a debug error.
Finally, I can't password protect the sheet because I have table in it. Excel won't extend tables if the sheet is protected. I would like to password protect the VBA so no one can view it, but is another post altogether.
In order to lock cells so that they cannot be edited, the sheet must be protected. What do you mean by "extend tables"? I tested the macro on some dummy data and it worked as you requested. Can you attach a copy of your file (de-sensitized if necessary)?
To protect your macros, you have to protect your VBA Project. Do the following:
-hold down the ALT key and press the F11 key to open the Visual Basic Editor
-click on 'Tools' on the top menu
-click 'VBAProject Properties'
-click the 'Protection' tab
-click the box to the left of 'Lock project for viewing' to put a check mark in it
-enter your password and then confirm it and click 'OK'
-close the VB Editor
-save your workbook as a macro-enabled file and close it
When you re-open the file, you will not be able to see the macros unless you enter the password. Keep in mind that this type of protection is not very strong and anyone who really wants to get at your macros can probably do it with a little research.
Hi, Mumps1.
If the sheet is protected, Excel won't allow additions to the table inserted into the sheet. I attached a dummy excel sheet. I need a VBA macro that will force N/A into column M and N if the client is DOGGIE. Subsequently the user cannot alter the N/A in columns M and N for that row for client DOGGIE. The sheet cannot be protected because there are daily additions to the table. Excel, to my knowledge, won't allow additions to a table if the sheet is locked.
How aboutPlease Login or Register to view this content.
Last edited by Fluff13; 11-16-2019 at 05:01 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks