#1
|
|||
|
|||
How to protect cells and still be able to use all data entry form
Hi,
I've created a data entry form in Excel 2016 for easy use for a couple of people. However, the spreadsheet has been corrupted several times by those inputting the data (records missing, data in wrong cells, etc). I've tried to lock the cells that already had data in them and then protect the sheet. But I can't use the data entry form to create a new record when I do this. I can only use criteria,find next,find previous and close. Is there a way that I can protect the cells in the spreadsheet that has data in it but still be able to use all functions of the data entry form (ie: specifically New, Delete, Restore) to create new records? Any other suggestions? Thanks, Jae |
#2
|
|||
|
|||
Last time I have used Excel forms was some 15 years ago, so I am not sure will it work or not, but you can try this:
Activate the sheet with data, and create form; Create an empty sheet, and move form there (this is where I am not sure how this works - remains the form linked to original sheet or not; Hide original sheet. In case you want only to prevent occasional entries directly into sheet, you can use simple Hide Worksheet option. In case you want to prevent any possibility the user is purposely making original sheet visible and then editing it, open VBA editor (Alt + F11), in sheet properties (in VBA project window at left select the sheet, below VBA project window Properties window must be visible - when not, then activate it from View menu) set sheets Visible property to xlSheetVeryHidden (the sheet is not listed in Unhide list when sheet is Very Hidden), Then right-click on <VBAProject (YourWorkbook)> in VBA project window, select VBA Project properties from drop-down, and protect the project with password. After you save your workbook now, on next opening whenever user tries Alt+F11, the password you entered when you protected VBA project, is asked. (Be sure you have stored this password somewhere - compared with worksheet passwords it is more difficult to hack.) When users must see data in table on original sheet, create a report sheet which reads data from original sheet (this can be the sheet to where you dragged the form). My advice is you use an ODBC query for this - users can refresh the query selecting refresh from right-click dropdown on QueryData table, or select Refresh All from Data menu. And you can set the query to be refreshed on opening. |
#3
|
|||
|
|||
Thanks ArviLaanemets for your reply. I will have to work with what you've docmented below, some of which I'm familiar with, some not. Will work on it the next couple of days and update if this is the resolution.
|
Tags |
data entry form, protect cells |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Data Entry Form Help | eddie.turner | Word | 1 | 12-31-2016 12:12 AM |
Formatting a Word Template using form for data entry | heykug | Word | 1 | 08-25-2016 04:55 PM |
Data Entry Form - Hide Click here to enter text. | sunrise06 | Word | 4 | 05-07-2015 08:44 PM |
Properties for data entry control on Form help | EricMannzi | Word VBA | 10 | 02-24-2015 09:48 AM |
Form field selection allows entry into form text box | David C | Word | 1 | 10-24-2012 04:53 AM |