Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Disallow cell data entry which autopopulates
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, -- jackel |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Disallow cell data entry which autopopulates
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 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, -- jackel |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Disallow cell data entry which autopopulates
Err, obviously you would not manually protect the sheet if you are
doing so via code. Please ignore step 3. On Feb 19, 3:21*pm, JP wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Disallow cell data entry which autopopulates
'JP[_5_ Wrote: ;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 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, -- jackel- 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! -- jackel |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Disallow cell data entry which autopopulates
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 On Feb 19, 6:08*pm, jackel wrote: 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! -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions | |||
How do I get data validation to disallow specific entries | Excel Worksheet Functions | |||
Data entry - Copy contents of cell typed in one cell to another ce | Excel Worksheet Functions | |||
Disallow cell entries | Excel Discussion (Misc queries) | |||
In Excel, how can I tell the program to disallow duplicate data i. | Excel Worksheet Functions |