Prevent Deletion Of A Cell With No Cell Protection

Oct 29, 2008

I have a worksheet with multiple entry boxes in a row. Depending on the value in one row, another row will either yield the result from a formula, or remain blank, and this cell needs to be locked. I was able to do this by first putting the formula into the destination cell, and then using custom data validation that says the cell value = "". So this prevents anyone from typing in that cell, no matter if it yields the formula result or must stay blank. What it doesn't protect against is someone deleting the contents of the cell. The reason I don't want to protect the cell is because I want people to be able to highlight the whole row past that cell and delete it all at once. So either using Data Validation, a formula, or VB programming I need to not allow someone to do ANYTHING to that cell, even delete the contents, without locking the cell. Hopefully it can be done, thanks!

For example, if E17 = "X", then K17 = INPUT!$M$28*J17. If E17 <> "X" then K17 is blank. Either way, I don't want any typing or deleting of K17. This applies for range K17:K91, and corresponding E17:E91.

View 14 Replies


ADVERTISEMENT

Stop Cell Deletion Without Protection

Nov 28, 2006

I have a cell with data validation: list, that allows values contained in named range. I want to disallow to delete contents of this cell (with 'del' key, or when i edit cell, delete text and then press 'Enter'). Is it possible to do with formula or VBA

View 4 Replies View Related

Prevent Deletion Of Row

Jun 11, 2014

I am trying to write a VBA code that prevents the user from deleting row 8.

I do not want to use the sheet protection with password approach.

I simply want a code that prevents the user from deleting row 8 and notifies him via MSGBOX that such action is prohibited.

View 1 Replies View Related

Prevent Sheet Deletion

Sep 3, 2005

Is it possible to prevent a particular sheet from being deleted?

I have a workbook that contains sheets (of course). One of the sheets
serves as a "Help" sheet. I don't want users to accidentally (or intentionally)
delete just this sheet.

View 10 Replies View Related

Prevent Some Worksheets From Deletion

Jan 5, 2007

i have a workbook with 6 worksheets in it. the user can put data into any cell in any worksheet. what i want to be able to do is prevent the user from accidently deleting any of the worksheets

View 8 Replies View Related

Prevent Deletion & Allow Entry

May 2, 2007

I am trying to complete a spreadsheet that will be shared by several users but have come across a slight problem. I need them to be able to enter text into blank cells but when the the text has been enetered for them not to be able to delete or edit it again in columns A & B. They should also not be able to delete rows or columns. I have tried with other peoples help but as soon as the spreadsheet becomes a shared document I get a run-time error 1004 when entering in any box

View 9 Replies View Related

Prevent Deletion Of Formula & Other Cells

Sep 12, 2007

i have made a document for other users to use. how can i limit the paste function to ONLY ALLOW PASTE SPECIAL-- i wish to aviod damage to formatting and formula etc

View 3 Replies View Related

Prevent Sheet Insertion & Deletion

Sep 14, 2007

Is it possible to make it so that a user cannot delete or add sheets to a work book, except through buttons assigned to macros? I've tried simply locking them, but these sheets need to be accessed to input and manipulate data. I have the macros necessary to delete/add sheets, but right now they are redundant since the user can simply do it manually.

View 3 Replies View Related

Prevent Worksheet Protection

Aug 18, 2007

I have a workbook for which I would like to protect the worksheets, while still allowing my code to alter the worksheets, which can be done with the line:

Sheet1.Protect Password="abc" UserInterfaceOnly:=True

However, I want allow some users (who know the password) to be able to unprotect the sheet if they need to edit it, then turn the protection back on after they are done. How can I make sure that they use my macro to protect the sheet with UserInterfaceOnly set to true, rather than the standard way to turn on protection?
That is, is there a way I can prevent the user from being able to protect the sheet with the Tools->Protection->Protect Sheet menu item?

View 2 Replies View Related

Prevent Workbook Protection In VB Code

Dec 1, 2011

I have a workbook that contains macros. When the workbook is protected, the macro returns an error so I have unprotected the workbook but, if someone protects the workbook and saves it, it returns an error when closing and even if they choose cancel, it still screws up the macro and prevents it from working upon reopen.

Is there a way to prevent someone from protecting the workbook in vb code?

View 7 Replies View Related

Macro Not Responding To Cell Deletion

Nov 12, 2008

if column E is "Annuity", you can type a value in column J and it runs a formula to calculate a value for column K. If E is not "Annuity" it clears the contents of J (which should clear K). What's not working is when column J gets cleared or manually deleted, column K must be blank. The worksheet_change event is not working for deletion of column J.

View 5 Replies View Related

Preventing Cell Reference Deletion

Nov 10, 2008

I have a workbook that contains 2 worksheets. The first worksheet contains all the data. The second sheet contains formulas that reference (absolute for all) the first sheet.

I run into problems (actually the users do) when the data set is updated with new information. Sometimes the users will delete all the data rows first and then copy the new data to the blank cells. When they do this, it messes up the formulas with #REF! error. Is there a way to prevent this? or is there a way to hard code the formulas so I don't get this error?

View 4 Replies View Related

Deletion Of Empty Characters From A Cell

Feb 25, 2007

I have a cell for example with:
"

11111
22222

44444

77777


"
What the effective way to delete all empty rows in current cell? I confused with VbCrLf, VbNewLine and e.t.c I want to get after that:

"
11111
22222
44444
77777
"

View 4 Replies View Related

Empty Cell Deletion At Bottom Of Worksheet

Sep 24, 2009

On some of my worksheets, the slider bar on the right has become a small square rather than a long rectangle. This causes this slightest shift downward to jump through large amounts of rows. Excel seems to think there must be data down there I need. How do I get rid of empty cells at the bottom of a worksheet so that this no longer happens?

View 2 Replies View Related

Cell Deletion (delete/remove All Non Use Cells)

Oct 6, 2008

is it possible to delete/remove all non use cells
for example if i only want the cells A1 THROUGH TO O31
all the rest removed??

View 5 Replies View Related

If Cell Is Not Empty How To Trigger Deletion In Other Cells

Jun 13, 2013

I have completed an automated time sheet but have realised that there are some days where overtime entries make it go wrong.

There is one column where you enter an overtime shift when the cell has an entry I need all the other calculations to be zero

A1 START TIME
B1 END TIME
C1 HOURS WORKED ARE CALCULATED using a formula
D1 OVERTIME COLUMN ( IF AN ENTRY IS PUT IN HERE MANUALLY I WANT C1 ALWAYS BE 0

I tried various ways with isblank but just get true or false.

View 2 Replies View Related

Code For Form Referring To Cell Deletion

Feb 6, 2007

This time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.

I need to know what code i would have on the 'delete' button of the form.

If you want me to describe it any further i would be happy to, just ask.

View 9 Replies View Related

Macro For Cell Deletion Relative To Msgbox

Mar 21, 2007

Im looking for code for a button that asks for a Record number, and it will delete the record. But the record number is written in cells as 'Record No.1' 'Record No.2' etc. But i want the messagebox to ask for a single number and it would delete that record. Now the position of the records is what will be the difficult bit about this code, but it has a pattern.

For Record No.1 i need deleting cells C2:J100
For Record No.2 i need deleting cells L2:S100
For Record No.3 i need deleting cells U2:AB100

If anyone could give me a bit of code that works for these 3 then i could waste a load of time filling the rest of them out.

View 2 Replies View Related

Worksheet Change Event Not Responding To Cell Deletion ...

Nov 13, 2008

I have a code where column K is not responding to a value being deleted out of column J. I've highlighted the portion of the code dealing with this. Deletion should trigger the worksheet_change event to clear out column K as it's supposed to. how to get deletion to take effect?

View 4 Replies View Related

Worksheet Change Event Not Responding To Cell Deletion

Nov 14, 2008

This is part of a macro in a worksheet_change event. When a cell in column J gets deleted by a user, the corresponding cell in column K should also clear. But it's not responding to the delete.

It DOES clear when the other 2 criteria are met (.cells(1,10) = 0 and .cells(i,5) <> "Annuity"). The worksheet_change event should pick up on the cell deletion, but it's not. And column J is already a trigger for the macro to run, so I'm not sure what's going on. Either the trigger is still wrong, the isempty(.cells(i,10)) is not correct syntax, or this event just doesn't respond to cell deletion.

View 6 Replies View Related

Cell Protection

Nov 5, 2008

is there a way that i can stop people being able to edit certain cell in a sheet but still allow them to type in other cells as when i have tried diffrent ways it locks the whole sheet

View 5 Replies View Related

Prevent Cell Entry Based On Another Cell

Oct 22, 2013

If A1 = "Yes"

Then don't allow input into cells B1 and C1. Or delete anything in B1 and C1 perhaps?

Cell A1 would be the result of a formula

How would I go about creating something like the above?

View 2 Replies View Related

Unlocked The Cell Protection

May 21, 2009

I have a workbook with several spreadsheets in it. My 1st sheet is a summary sheet, the rows represents the sum total find each worksheet, normally i will just clk on the cell in the summary sheet and clk on the worksheet i need and clk on the total.

It always worked but suddenly won't respond. When i put= in a cell in the summary sheet and clk on the sum total of the other worksheets, it won't clk, it's like it has been frozen. I unlocked the cell protection but it still won't respond when i clk on any cells on the worksheets.

View 8 Replies View Related

Cell Protection & Clear Macro

Oct 14, 2008

when i password protect my sheet it doesnt let my clear cells macro work on the cells i need it to clear why is this and what way is there around this completes my complex sheet.

View 2 Replies View Related

Cell Protection After Entering Data

Mar 1, 2012

Having the cell protected after entering the data. From the below table, i need when a date is entered and moved to next cell, the cell which contains the data should be protected. I need to this for the comments column as well.

Number
DateStart timeEnd TimeActual Time takenComments
123459132546132156135469654814846543

View 1 Replies View Related

Formula Protection For Cell Entry

Feb 11, 2004

I'm trying to set a cell so that it has a certain value based on other cell(s) information. I can put a formula into the cell to do that, but I also want to make the cell available for user input, preserving the formula should the user change their mind and delete their input.

Basically, it's a cell with a protected formula that can be overridden with user input, then if the user decides to take their input back out, the formula kicks back in.

View 9 Replies View Related

Cell & Sheet Protection Basics

Mar 27, 2008

I have 2 spreadsheets: in a Sheet1 I keep some values and in a Sheet2 5 names of users with special rights (range A1:A5). I want to have sure that anybody will change/delete that names without password. Only the user "Administrator" can do that(I check the name of the user using "Application.UserName"). Additional question: is it possible to still protect those cells if somebody turn off macros during opening file?

View 4 Replies View Related

Change Cell Colour When The Protection Is Locked

Dec 3, 2009

Is there any way to change the pattern color of a cell (or a group of non-adjacent cells), say from No Color into Red if the cell(s) protection is locked (or unlocked)? Seems to me that the Conditional Formatting cannot work this way.

View 3 Replies View Related

Toggle/Switch Cell Locking/Protection On/Off

Sep 4, 2006

I work in the field of education and the spreadsheet I'm working will require pupils to enter answers into the spreasheet. When a command button (New Questions) is pressed I want the VBA code to switch the cell protection (locked) on or off depending on a random value in cell c2. If c2 = 1 I want f3 to be locked, if c2 = 2 then d3 needs to be locked, but not f3. I've attached a copy of the spreadsheet to this email

View 5 Replies View Related

User Allow To Format The Cell And Text In Protection Sheet ?

Jun 1, 2006

how to allow formating a text and cell in protect sheet ?

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved