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.

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" <(E-Mail Removed)> wrote in message
news:f2bsjf$mb$(E-Mail Removed)...
> 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.
>
>

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.
>
>
>

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.
> >
> >
> >

"NickHK" <(E-Mail Removed)> 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.