How do I protect only formulas in Excel?

[Guest]

Hi, I am looking for a solution to be able to allow some cells that are
unlocked to have other users enter data into them, however I don't wan't
other users to be able to enter formulas without a password. I have heard of
this being done before but I didn't know if it was an option I've overlooked
or some kind of advanced VB code.

Any help is greatly appreciated,

RH

 

[Guest]

Hi,

Just an idea:
try catching OnChange event and check if = is entered and then popup a
password dialog

Hope this helps.

Peter

 

[Guest]

Hi

Unprotect the cells where you want to allow entries, then click on
Tools|Protection|Protect sheet. Type in a password and untick Select locked
cells.

Click on OK, and when asked, re-enter the password

 

[Guest]

That sounds pretty logical Peter, any idea on the syntax for that?

Thanks for the replies!

 

[Guest]

Hi,

Use the SelectionChangeEvent:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If (Left(Target.Value,1) = "=") Then
' user is trying to enter a formula - do something
End If
End Sub

 

[b&s]

.... Hi Peter, maybe this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
If Left(Target.Formula, 1) = "=" Then
' user is trying to enter a formula - do something"
End If
End Sub


--
pozdrav!
Berislav

Always nice to hear if a suggestion works or not.
***********************************************************
ROT13 - email address (e-mail address removed)-pbz.ue

 

[Guest]

Hi Berislav,

Well, basically in this case it doesn't matter if you use .Value or .Formula

Range("A1").Value = "=A3+A4"
Range("A2").Formula = "=A3+A4"

Both of them have same result

Regards,
Peter

 

[b&s]

Hi Peter,

Both of them have same result

I'm not 100% sure

--
pozdrav!
Berislav

Always nice to hear if a suggestion works or not.
***********************************************************
ROT13 - email address (e-mail address removed)-pbz.ue

 

[Guest]

Well, if you use .Value to specify a formulae (e.g. Value = "=A1+A2") and you
check if formulae exist (Range(..).HasFormula) you will get True.

Maybe an MVP will describe if there is a difference.

 

[b&s]

.... but we are speaking about:
If Left(Target.Value,1) = "=" Then ...
and
If Left(Target.Formula, 1) = "=" Then ...

maybe I'm wrong, but if I have the cell with formula: "=A1+A2" (A1 and A2
are empty) then Left(Target.Value,1) gives as result: "0", until
Left(Target.Formula, 1) gives: "="

 

[Dave Peterson]

I think for the majority of cases, it probably won't matter. But personally, I
like to user .formula when I'm plopping in the formula. I'll use .value when
I'm putting in a constant value.

But since some people actually use '=============== as a separator (for visual
effect), I find it much better to check .hasformula than =left(.formula,1)

For instance:

Option Explicit
Sub testme()
With Range("A1")
.NumberFormat = "General"
.Formula = "=b1+b2"
MsgBox .HasFormula & "--" & Left(.Formula, 1)
.NumberFormat = "@" 'Text
.Formula = "=b1+b2"
MsgBox .HasFormula & "--" & Left(.Formula, 1)
End With
End Sub

 

[b&s]

Hi Dave,
thank You for Your commentary. My remark was that: Left(Target.Value,1) is
not relevant formula for equation: If Left(Target.Value,1) = "=" Then ...
(testing cells with formulas), therefore my recommendation, for that case,
was: Left(Target.Formula, 1)

 

[Guest]

Hi Berislav,

Yes, that's true - but the question was how to protect empty cells and don't
let users to enter a formula, so I was supposing that the cells are empty and
there are no formulas in the cells.

The solution Left(Value, 1) will fail if you try to check the cells that
already contain formulae - but if you're checking the user input the solution
will behave the same as Left(Formula,1).

Regards,
Peter

 

[b&s]

Hi Peter,
did you test your macro? I reacted because your macro does not work for me!

 

[Dave Peterson]

But it was relevant to this portion of your post:

If Left(Target.Formula, 1) = "=" Then ...

This can give misleading results.


Just the relevant portion of my post.

Option Explicit
Sub testme()
With Range("A1")
.NumberFormat = "@" 'Text
.Formula = "=b1+b2"
MsgBox .HasFormula & "--" & Left(.Formula, 1)
End With
End Sub

 

[b&s]

Hi Dave,
I didn't comment your post and your recommendation ... who am I?
I only tried to explain my answer to OP regarding his macro.

 

[Guest]

First off, I'd like to thank every one who has replied. I sincerely
appreciate the help.
I've now tried the formula on the sheet and I get an error stating
"False--=" I am definitely not up to par on my VB coding but I'm assuming
that this is the line

MsgBox .HasFormula & "--" & Left(.Formula, 1)

Seems like that would work though. Any suggestions?

Great Ideas everyone, thanks again!

 

[Dave Peterson]

That wasn't an error message. It was just displaying that just because a cell
starts with an equal sign, it doesn't always contain a formula.

If you want to check to see if a cell contains a formula, I think the best thing
to do is look at the .hasformula property.