Allowing input, but restricting deletion

Hi all,

Is it possible to allow input into a spreadsheet, but at the same time
making sure that data that is inputted is impossible to delete?

Hope you guys can help,
Roland

0
6/7/2007 5:56:30 PM
excel 39879 articles. 2 followers. Follow

2 Replies
394 Views

Similar Articles

[PageSpeed] 45

You may be able to do something with some sort of worksheet event.  But I'd be
worried about typos.  How would you go about fixing those if the cell was locked
after I finished my initial input?

innovate wrote:
> 
> Hi all,
> 
> Is it possible to allow input into a spreadsheet, but at the same time
> making sure that data that is inputted is impossible to delete?
> 
> Hope you guys can help,
> Roland

-- 

Dave Peterson
0
petersod (12004)
6/7/2007 6:45:27 PM
Not without some event code that would prevent the deletion after input.

Right-click on the sheet tab and "View Code".

Copy/paste the code into that module.

Alt + q to return to Excel window.

CTRL + a to select all cells.

Format>Cells>Protection........uncheck "locked"

As written, the event code will operate on any cell in columns A:D to lock the
cell after data input.  Options for other ranges can be provided to suit.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column < 5 Then
ActiveSheet.Unprotect Password:="justme"
       If Target.Value <> "" Then
       Target.Locked = True
        End If
    End If
enditall:
Application.EnableEvents = True
ActiveSheet.Protect Password:="justme"
End Sub

To prevent users from seeing the code and password, protect the project from
viewing.

Alt + F11 to open Visual Basic Editor.

Select your workbook/project and right-click>VBAProject
Properties>Protection>Lock for Viewing.  Apply a password and save/close the
workbook.

When re-opened, code will be unviewable or uneditable.


Gord Dibben  MS Excel MVP

On Thu, 07 Jun 2007 10:56:30 -0700, innovate <roland.aspiras@gmail.com> wrote:

>Hi all,
>
>Is it possible to allow input into a spreadsheet, but at the same time
>making sure that data that is inputted is impossible to delete?
>
>Hope you guys can help,
>Roland

0
Gord
6/7/2007 7:31:49 PM
Reply:

Similar Artilces:

Entire Active Directory visible when Allowing Users to Edit Ranges
Excel XP on Windows 2000. TS Environment running Citrix. Following Problem: Tools - Protection - Allow user to edit ranges - new - permissions - add. This provides a list of the ENTIRE active directory of the domain. Where is the switch to turn this off or to limit the access? Am I in the right newsgroup even? Any hints will be greatly appreciated. ...

Deleting rows meeting criteria
I know that this question has been asked but I can't seem to fit the answers I have found with my problem (because of my ignorance) I need to delete all rows that have the words Employee, Spouse or Child. Then I want to delete all empty rows. I have a macro that deletes the empty rows but if I could do it all in one, it would be better Thank you very much Vickie Vickie, see if this will work for you Sub Delete_Rows() ' This macro deletes all rows on the active worksheet ' that have Employee, Spouse, Child, or is blank in column A. Dim rng As Range, cell As Range, del As Range Set...

Locking all but a few data input cells
I have a worksheet that takes a few input parameters and does a bunch of calculations. I would like to protect the cells that are not for data entry so I don't accidentally overwrite a formula I spent hours debugging. ;-) I am only interested in protecting the worksheet from myself at this time, so if there is a simple, but less secure way, I would be interested in that. By that I mean a way to easily lock and unlock individual cells while I am still making changes. I would also be interested in a more secure method for later. Here's what I have done so far. I am using Excel 2007. ...

Does Outlook 2003 allow multiple Exchange accounts?
Hi, I was wondering if Microsoft figured out a way to have more than one Exchange accounts in Outlook 2003? I realize that I can have multiple Exchange accounts in Outlook 2002 provided that I set them up as POP3 or IMAP. I'm talking about having multiple pure Exchange accounts w/ calendar and everthing. Thanks, Sam Nope. Only 1 Exchange account is still supported when working against 2 or more sites. -- Neo [MVP Outlook] Due to the Swen virus, all e-mails sent to this account will be deleted w/out reading. "Sam" <sam@iqinternet.com> wrote in message news:e1sEVVao...

field service allow setup of cycle charge allowed up to
Field service needs to allow the setup in meter readings to have an amount for each meter reading that is not charged versus just a maximum in the base field. For instance the customer each meter reading gets the first 1000 cycles at no charge and .05 per cycle over the allowance. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsre...

can accepting a meeting for one participant delete another
can accepting a meeting for one participant delete another ...

Restricting pasting, except for Paste Values
Within this Discussion Group, I have searched for "restrict pasting" and "prevent pasting", but I have been unable to find a thread that solves my problem. I have created a worksheet in which I have added conditional formatting and validation rules to various cells. Most of my users typically copy data from an Outlook e-mail message, a Word document, or a PowerPoint file and paste it into my worksheet. When they do this, however, the conditional formatting and validation rules get overwritten. I reached out to this Discussion Group and asked if there was...

Tabbing through input worksheet
Rather than creating a complex form with all the associated VBA coding I have created a worksheet which will transfer information to another sheet - using one macro The input worksheet has input boxes all over the place and I was wondering if it is at all possible to tab or use enter to jump from cells A1 to C4 to D16 etc etc. similar to the way that a created form works Any help would be appreciated Thanks Brian A simple way without using VBA would be to protect all the cells in the worksheet from being selected expect the input cells. Then a tab would go from one ...

read only form allows date to change
I have a form that I open in read only that has a calendar popup for the date fields. When I open the form the calendar pop up allows the date to be changed. The rest of the fields are read only. Any suggestions. Thanks Private Sub ViewActivityNote_Click() On Error GoTo Err_ViewActivityNote_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "FRM - Activity Note" stLinkCriteria = "[activitynoteid]=" & Me![activitynoteid] DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormReadOnly Exit_ViewActivityNote_Click: Exi...

NUL characters are not allowed
Hi, We have install the exchange 2003 and we are encountering intermitten problem when sending email to internet (only to a certain email domain other are alright). When that happens, we receive a "NUL characters ar not allowed" error. <mail.anonymous.com #5.5.0 smtp;550 Requested action not taken: NU characters are not allowed.> This error usually happens when we reply to a certain email domain. When this happens, we would retry 2 or 3 times, it usually i successfully after 2 or 3 tries. It only affects a small number of email domain. Other email domain ha no problem. H...

Deleting Cells, but not ones with formulars
Hello, I have a large sheet that people enter data into each week. I want t be able to hightlight a large area of the sheet and delete the data i those selected cells, but not the formulars that are in some of th cells. Anyone know how I can do this? Any help apreciated! -- scriblesvur ----------------------------------------------------------------------- scriblesvurt's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2439 View this thread: http://www.excelforum.com/showthread.php?threadid=38001 This worked for me: Sub DeleteCells() Set rng = Range("...

Payroll should allow concurrent builds by different users
In a company that is heavy payroll, all checks are processed in the same day but by different users for different subsets of employees. Allowing concurrent builds would be nice. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolution...

what does whitespace not allowed in this location mean and how do.
i am importing data in csv to excel but it says 'file not loaded- whitespace not allowed in this location' what does that mean? and how do i cahnge it? ...

time for message restrictions to take effect
Hi all. After i set message restrictions in a user account properties via active directory, i had to wait about 5-10 min before the restriction can take effect. Restarting microsoft exchange services and refreshing in active directory does not help. Is there a way i can shorten the waiting time? I am using exchange 2003 in win2003. Thks in advance. On Mon, 2 Oct 2006 10:43:01 -0700, inenewbl <inenewbl@discussions.microsoft.com> wrote: >Hi all. After i set message restrictions in a user account properties via >active directory, i had to wait about 5-10 min before the restrict...

Allowing pictures to download in Outlook 2002
My boss is using Outlook 2002 and I can't find this setting: Pictures don't download in his emails In 2003 it's under Tools - Options - Security - Change Automatic Download settings. Can anyone help? Thanks! ...

Formulas containing variable input
Hi guys: I suck at Excel, so I would really appreciate any help. Here's the deal, I have a huge spreadsheet in which each column contains the data pertaining to each month. for example B is january, C is February, and so on... I have a separate sheet where calculations are carried out, and one such calculation requires summing up the columns of data from all previous month including the current month. for instance, if it were march, I would have someth like =Sum(B9:D9), for the month of june i would have =Sum(B9:G9). it's a pain changing the formula manually every month, so is there a...

Rogue Mailboxes won't Delete
I am trying to remove my Exchange 2000 Server from the network. Every thing has been moved to the new server and anything that wasn't going to be moved has been deleted. Or so I thought. I have two mailboxes in one of my mail stores that have not yet been deleted. I even tried purging them from the System Manager and they will not go away. I know I can't uninstall Exchange if there are still mailboxes. One mailbox was corrupt and currently has no user associated with it. The other for some reason has a Last Logon of 11/2002 which is not correct because the employee was here and ...

Converting Excel Data Input to csv format
I wish to create a Data Input Form. I would like to use Excel 2000 for = the form. The data is to be imported into Sage Line 50, an accounting = package and needs to be imported in a csv format. Tips and any useful links to online Help notes please. ~~~~ Gerry Can't you just save the file as a CSV file, using Excel's Save As option. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Gerry Cornell" <gcjc@btinternet.com> wrote in message news:OqxS%23CAQEHA.3380@TK2MSFTNGP11.phx.gbl... I wish to create a Data Inp...

If field value has changed allow an audit
Hi, Some will have objections to what I am doing and call it messy - but anyway. If a field has had its value changed I want to be able to continue with the audit if it has not then - allow the form to close without saving. So I have added the following to the forms beforeUpdate. The two fields that I want to check are numeric Me!Eng_Cost and Me!Ext_Cost I have tried the .Dirty and it is not intended to work here. So Private Sub Form_BeforeUpdate(Cancel As Integer) ' Confirm with user that this record is to be modified Dim updRecord As Byte updRecord = MsgBox(...

Don't allow checks in Payroll to be dated back to a closed year.
Clients occationally enter a check date in error there are not errors or warnings to provent them from posting the check back to the closed year. There are safeguards for Financial to keep it from posting to the GL, but not payroll. Luckily the client can remove the 2004 year end file, void the checks and then re-create the year end file. We would like to see some prevention in this area. You have marked the periods in the prior year closed for the Payroll module? "JJONES from InterDyn-Artis" <JJONES from InterDyn-Artis@discussions.microsoft.com> wrote in message ...

Can't delete NDR
Strange problem with 1 mail item. The user (Outlook 2002 in Internet Only / Win XP Pro w/SP1) received a "Mail Delivery Failed: Returning Message to Sender" mail item. The problem is that it registers as 9 MB and can't be deleted. If you highlight, click on, or right click on the message, Outlook CPU utilization goes through the roof and nothing can be done. Anyone seen this? In Addition: Scanned mail with 2 virus scanners and Adaware. Nothing. >-----Original Message----- >Strange problem with 1 mail item. The user (Outlook 2002 >in Internet Only / Win X...

Deleting transactions #2
Help! We had problems with one of our registers and we had to replace the PC that we were using and and when we finally got all ready to operate we lost everything that we had rung in (which we re-entered later) but now when we run a detailed sales report for that day it shows all of the sales that we cannot see in the journal but they show on the report, I want to delete them but there is not a batch for those transactions only the transaction # and I do not want to delete all of the transactions just the ones that are incorrect for that day. I hope someone can help Thanks! You can v...

How can set the input focus for the Edit Box of another process
::SendMessage (hWnd, WM_SETFOCUS, 0, 0); it can set the input focus for the ComboBox of another process, but can't for Edit Box, Why? Best Regard! WRONG! This only tells the control that FOCUS HAS ALREADY BEEN SET! It does not, itself, change the focus!!!! This is one of the most common misconceptions a lot of programmers develop, and usually arises from failing to read the documentation. Note that it gives the *illusion* that focus has been set without actually setting it! Calling the SetFocus method on the window (or using the ::SetFocus API call) will set the focus. A *side e...

Allow multiple Select
Also, I'm having problems working with multiple select from CFileDialog. I have trouble accessing the files. I can't quite understand what itmeans by: "replacing m_ofn.lpstrFile with a pointer to a buffer you have allocated" thanks for your help CFileDialog dlg TCHAR szFiles[1000]; dlg.m_ofn.lpstrFile = &szFiles "Henry" <abshhkc@bath.ac.uk> wrote in message news:Hrr8B9.513@bath.ac.uk... > Also, I'm having problems working with multiple select from CFileDialog. > > I have trouble accessing the files. I can't quite understand what it...

Mass Calendar Deletion
I need to keep someone else's calendar informtion in my copy of Outlook temporarily in order to transfer it between two iPAQs, but I'd like to get rid of it when I'm done. Is there any way I can delete all of the appointments in my calendar? (Short of selecting and deleting each one individually) -Jason yeah, just change to 'category view' then you can see them all and hence delete them in one go. "Jason" <jasons@noink.com> wrote in message news:0bf501c36bda$30f597f0$a501280a@phx.gbl... > I need to keep someone else's calendar informtion in my...