Microsoft Office Forums How to protect cells and still be able to use all data entry form

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-09-2019, 09:00 AM
Jae Jae is offline How to protect cells and still be able to use all data entry form Windows 10 How to protect cells and still be able to use all data entry form Office 2016
Novice
How to protect cells and still be able to use all data entry form
 
Join Date: Nov 2019
Posts: 2
Jae is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 11-11-2019, 12:28 AM
ArviLaanemets ArviLaanemets is offline How to protect cells and still be able to use all data entry form Windows 8 How to protect cells and still be able to use all data entry form Office 2016
Expert
 
Join Date: May 2017
Posts: 508
ArviLaanemets has a spectacular aura aboutArviLaanemets has a spectacular aura about
Default

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.
Reply With Quote
  #3  
Old 11-13-2019, 05:32 PM
Jae Jae is offline How to protect cells and still be able to use all data entry form Windows 10 How to protect cells and still be able to use all data entry form Office 2016
Novice
How to protect cells and still be able to use all data entry form
 
Join Date: Nov 2019
Posts: 2
Jae is on a distinguished road
Default

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.
Reply With Quote
Reply

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
How to protect cells and still be able to use all data entry form 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
How to protect cells and still be able to use all data entry form Form field selection allows entry into form text box David C Word 1 10-24-2012 04:53 AM


All times are GMT -7. The time now is 11:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft