Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Prevent formula entry
Hi,
I have a workbook which I want to prevent users from entering formulae globally across the entire workbook. There are however formuale already entered into the workbook but want to prevent any new formulae being entered. Can this be accomplished? How would I do this? Many thanks. Ian E. |
#2
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Prevent formula entry
Nothing built-in, but you can check either at the WS or WB level:
Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Application.EnableEvents = False For Each Cell In Target.Cells If Cell.HasFormula = True Then Cell.Value = "" MsgBox "No formulae allowed" End If Next Application.EnableEvents = True End Sub NickHK "news.freedom2surf.net" wrote in message ... Hi, I have a workbook which I want to prevent users from entering formulae globally across the entire workbook. There are however formuale already entered into the workbook but want to prevent any new formulae being entered. Can this be accomplished? How would I do this? Many thanks. Ian E. |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Prevent formula entry
You need to use worksheet protection to keep cells you don't want to be
altered from being altered. Cells that are Locked cannot be altered once worksheet protection is enabled. All cells are locked by default in a new workbook, so your first task is to identify all cells that you DO want your users to be able to use and unlock them. This is done via Format | Cells and clearing the Locked option on the [Protection] tab in that dialog. Any cells that are associated with controls and any that are altered via VBA coding will also need to be unlocked. Workbook/worksheet protection is accessed through Tools | Protection. Keep in mind that even using a password for protection is not 100% in Excel. There are lots of workbook/worksheet password cracking programs available that are effective and fast! But for the typical use, they suffice. "news.freedom2surf.net" wrote: Hi, I have a workbook which I want to prevent users from entering formulae globally across the entire workbook. There are however formuale already entered into the workbook but want to prevent any new formulae being entered. Can this be accomplished? How would I do this? Many thanks. Ian E. |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions,microsoft.public.excel
|
|||
|
|||
Prevent formula entry
I guess while I was typing, so was NickHK - and it causes me to add this:
he's right in that people would be able to enter formulas into unlocked cells. If your intent is to prevent adding formulas anywhere, then perhaps a combination of my recommendation and a modification of his to examine the cells you know to be unlocked would work for you. "JLatham" wrote: You need to use worksheet protection to keep cells you don't want to be altered from being altered. Cells that are Locked cannot be altered once worksheet protection is enabled. All cells are locked by default in a new workbook, so your first task is to identify all cells that you DO want your users to be able to use and unlock them. This is done via Format | Cells and clearing the Locked option on the [Protection] tab in that dialog. Any cells that are associated with controls and any that are altered via VBA coding will also need to be unlocked. Workbook/worksheet protection is accessed through Tools | Protection. Keep in mind that even using a password for protection is not 100% in Excel. There are lots of workbook/worksheet password cracking programs available that are effective and fast! But for the typical use, they suffice. "news.freedom2surf.net" wrote: Hi, I have a workbook which I want to prevent users from entering formulae globally across the entire workbook. There are however formuale already entered into the workbook but want to prevent any new formulae being entered. Can this be accomplished? How would I do this? Many thanks. Ian E. |
#5
Posted to microsoft.public.excel,microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Prevent formula entry
"NickHK" wrote...
Nothing built-in, but you can check either at the WS or WB level: Private Sub Worksheet_Change(ByVal Target As Range) Dim Cell As Range Application.EnableEvents = False For Each Cell In Target.Cells If Cell.HasFormula = True Then Cell.Value = "" MsgBox "No formulae allowed" End If Next Application.EnableEvents = True End Sub Note that this can be defeated easily by disabling macros. Personally, I can't think of any good reason to prevent users from making any valid entries, constants or formulas. It won't prevent users from trying to find values in hidden worksheets/rows/columns. All they'd need to do is open a new workbook and enter formulas with external references into the 'protected' workbook. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Prevent a Weekend date entry | Excel Worksheet Functions | |||
Prevent formula entry | Excel Worksheet Functions | |||
Prevent entry in a column | Excel Worksheet Functions | |||
How can I prevent loosing formula during data entry? | Excel Discussion (Misc queries) | |||
How to prevent double entry in excel? | Excel Discussion (Misc queries) |