Locking cells

[apar93]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Item
[/TD]
[TD]Qty
[/TD]
[TD]Price
[/TD]
[/TR]
[TR]
[TD]Stapler
[/TD]
[TD]2
[/TD]
[TD]$50
[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[Rick Rothstein]

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.

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.

 

[Rick Rothstein]

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.

 

[apar93]

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.

 

[Rick Rothstein]

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.

 

[apar93]

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.

 

[Rick Rothstein]

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.

 

[apar93]

Thank your for your suggestion, I will do that.

 

[apar93]

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?

 

[Rick Rothstein]

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.

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