Disallow cell data entry which autopopulates

[jackel]

I have a macro which autopopulates a date/time stamp (lets say,cell B1)
when a selection is chosen in (A1). This time stamp can be changed and
I don't want anyone to be able to change it. I want to make the cell
where the user can click the cell, but when they try to type, nothing
happens.

Thanks,

 

[JP]

You didn't say what Excel version you are using. In Excel 2003, you
would lock the cells you want to protect, and unlock the ones you want
the user to edit. Then you would protect the worksheet in the
Workbook_Open event, with additional code to allow only macros to make
changes to the locked cells.

1. Select cells you don't want to be changed (i.e. B1), press Ctrl-1,
go to Protection tab, check "Locked" and "Hidden"
2. Select cells you want to allow editing, press Ctrl-1, go to
Protection tab, UNcheck "Locked" and "Hidden"
3. Go to Tools>Protection>Protect Sheet>OK.

Now all the cells you marked in step 2 will be editable, while the
ones you selected in step 1 will be uneditable (and the formulas will
be hidden as well).

This code, placed in the ThisWorkbook module, will protect your sheets
and allow your update macro to run on the locked cells. See
http://www.rondebruin.nl/code.htm for implementation help.

Private Sub Workbook_Open()
Dim ws As Excel.Worksheet
For Each ws In Worksheets
ws.Protect Password:="mypassword", UserInterFaceOnly:=True
Next ws
End Sub


HTH,
JP

 

[JP]

Err, obviously you would not manually protect the sheet if you are
doing so via code. Please ignore step 3.

 

[jackel]

;2635396']You didn't say what Excel version you are using. In Excel
2003, you
would lock the cells you want to protect, and unlock the ones you want
the user to edit. Then you would protect the worksheet in the
Workbook_Open event, with additional code to allow only macros to make
changes to the locked cells.

1. Select cells you don't want to be changed (i.e. B1), press Ctrl-1,
go to Protection tab, check "Locked" and "Hidden"
2. Select cells you want to allow editing, press Ctrl-1, go to
Protection tab, UNcheck "Locked" and "Hidden"
3. Go to ToolsProtectionProtect SheetOK.

Now all the cells you marked in step 2 will be editable, while the
ones you selected in step 1 will be uneditable (and the formulas will
be hidden as well).

This code, placed in the ThisWorkbook module, will protect your sheets
and allow your update macro to run on the locked cells. See
http://www.rondebruin.nl/code.htm for implementation help.

Private Sub Workbook_Open()
Dim ws As Excel.Worksheet
For Each ws In Worksheets
ws.Protect Password:="mypassword", UserInterFaceOnly:=True
Next ws
End Sub


HTH,
JP


On Feb 19, 1:57*pm, jackel (e-mail address removed)
wrote:-
I have a macro which autopopulates a date/time stamp (lets say,cell
B1)
when a selection is chosen in (A1). This time stamp can be changed
and
I don't want anyone to be able to change it. I want to make the cell
where the user can click the cell, but when they try to type, nothing
happens.

Thanks,

I am using 2003, After I finished with everything and saw the auto
stamp could be edited, I would protect the shared work book and the
code was not able to be viewed. When unprotected, I right click on the
first page to work on the code.

I will try inserting the code you showed me and see how it works, I do
keep the auto stamp cells locked. Then when I protect the book the auto
stamp won't work, I wil let you know either way when everything is
good.

Thanks for the help!

 

[JP]

If you protect the worksheet via code with the "userinterfaceonly"
property, the macro should still be able to update the locked cell.
Let us know!

--JP