Locking cells

apar93

New Member
Joined
Nov 8, 2017
Messages
33
I need to lock certain cells in a chart however I also need to be able to insert rows into this same chart. Is this possible.

Example:

Item
Qty
Price
Stapler
2
$50

<tbody>
</tbody>

Take this chart as an example. I have locked the price cell as it contains a formula I do not want changed. I need to be able to copy the row (formulas included) and insert additional lines. It seems I cannot this when I lock the price cell. I hope this makes sense. Help please!
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Perhaps you could use the following VBA event code? I have assumed your Price column is Column C (change as needed). Any attempt to change a value in Column C will result in the action being undone.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("[B][COLOR="#0000FF"]C[/COLOR][/B]")) Is Nothing Then
    Application.Undo
  End If
End Sub

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
Perhaps you could use the following VBA event code?
I should have mentioned with the code I posted above... there is no need to lock the cells in Column C as the code is handling the "locking" of the cell's content... the code will work properly whether the cells are locked or not.
 
Upvote 0
I am very new to excel and I am having issues following your directions. I am stuck in the beginning where I need to select "view code". Which name tab am I right-clicking? I'm so confused! Appreciate your help!

Thanks.
 
Upvote 0
I am very new to excel and I am having issues following your directions. I am stuck in the beginning where I need to select "view code". Which name tab am I right-clicking? I'm so confused! Appreciate your help!
The tab is located at the worksheet. Select the worksheet with your chart, then locate the tab at the bottom of the worksheet (on a new workbook, the worksheet tabs would read "Sheet1", "Sheet2", "Sheet3", etc.)... that tab on the worksheet you selected is what you right-click on... when you do so, a popup menu will appear and one of the items on that menu will say "View Code"... you select that item to bring up the code module for that particular worksheet.
 
Upvote 0
When I right-click "sheet 1" which is were my chart is located the pop up menu reads...
Insert sheet, delete, rename, move or copy, select all sheets, protect sheet, tab color, and hide.
If it helps I'm working with Excel 2011 Mac.
 
Upvote 0
If it helps I'm working with Excel 2011 Mac.
Unfortunately, I know absolutely nothing about Mac computers or the software that runs on them. I am afraid you will have to wait for someone familiar with Macs to come along. By the way, for future question you may ask here, you should mention that you are using a Mac computer (wouldn't hurt to put that in the thread's title as well), that way people familiar with your type of system will be the one's that respond to you. Unless told otherwise, the assumption will be that you are using a PC, not a Mac.
 
Last edited:
Upvote 0
I was able to find a pc to work on. I used the VBA Code you provided. However, it doesn't work. It still allows me to type into the "C" cell. Any suggestions?
 
Upvote 0
I was able to find a pc to work on. I used the VBA Code you provided. However, it doesn't work. It still allows me to type into the "C" cell. Any suggestions?
It worked for me when I tested it before posting it and it just worked for me when I tested it a few moments ago. Did you remember to right click the tab whose Column C you wanted to protect and select "View Code" from the popup menu that appeared and then place the code in the code window that appeared? By the way, the code I posted early (once you get it working) has a flickering effect in the cell as the Undo is being performed... the following code eliminates that flicker.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Columns("C")) Is Nothing Then
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
  End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top