How to Foolproof data entry? DataForm

[jdog089]

I am trying to make a worksheet protected from users manuall
entering/editing data.

I would like them to ONLY be able to Add/Edit data through the Data -
Form utility.

Does anyone know if there is a way to only allow inputting/editing o
cells to only be done by Data -> Form?

(if you protect the worksheetm DataForm won't allow Add/Edit either)


Thanks :

 

[Maurice]

Not totally sure about forms, but i presume there is some sort of o
action event. If you had a macro that ran that would unlock the for
and relock it after the data was entered that would be close to wha
you want

Sub ProtectSheet()
Dim password 'This line of code is optional
password = "xyz"
ActiveSheet.Protect password, True, True, True
End Sub

Sub UnProtectSheet()
Dim password
password = "xyz"
ActiveSheet.Unprotect password
End Su

 

[Dave Peterson]

I like this idea. Maybe the OP could put a button from the forms toolbar on the
worksheet and assign it a macro like this:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Select
.Unprotect Password:="hi"
Application.DisplayAlerts = False
.ShowDataForm
Application.DisplayAlerts = True
.Protect Password:="hi"
End With
End Sub

(I used auto_open so that when you open the workbook, the data|form will be
shown.)

Don't forget to lock the VBA Project, too. Else you'll have inquisitive types
looking at your code and seeing the password.

Inside the VBE, you can lock the project.
Tools|VBAProject Properties|Protection tab.
Give it a memorable password and lock the project for viewing.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[jdog089]

Thanks guys,

this is perfect, exactly what i was looking fo