How do I protect only formulas in Excel?
 

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

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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

"RHmcse2003" wrote:

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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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
--
http://blog.jausovec.net


"b&s" je napisal:

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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.

--
http://blog.jausovec.net


"b&s" je napisal:

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

.... 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: "="

--
pozdrav!
Berislav

Peter Jausovec wrote:
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.


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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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




Peter Jausovec wrote:

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.

--
http://blog.jausovec.net

"b&s" je napisal:

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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





--

Dave Peterson

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)


--
regards/pozdrav!
Berislav


Dave Peterson wrote:
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




Peter Jausovec wrote:

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.

--
http://blog.jausovec.net

"b&s" je napisal:

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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
--
http://blog.jausovec.net


"b&s" je napisal:

.... 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: "="

--
pozdrav!
Berislav

Peter Jausovec wrote:
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.


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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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


--
pozdrav!
Berislav

Peter Jausovec wrote:
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

.... 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: "="

--
pozdrav!
Berislav

Peter Jausovec wrote:
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.


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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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 wrote:

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)

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
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




Peter Jausovec wrote:

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.

--
http://blog.jausovec.net

"b&s" je napisal:

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

--

Dave Peterson

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.

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
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 wrote:

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)

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
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




Peter Jausovec wrote:

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.

--
http://blog.jausovec.net

"b&s" je napisal:

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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!

"b&s" wrote:

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.

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
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 wrote:

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)

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
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




Peter Jausovec wrote:

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.

--
http://blog.jausovec.net

"b&s" je napisal:

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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

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.



RHmcse2003 wrote:

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!

"b&s" wrote:

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.

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
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 wrote:

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)

--
regards/pozdrav!
Berislav

Dave Peterson wrote:
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




Peter Jausovec wrote:

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.

--
http://blog.jausovec.net

"b&s" je napisal:

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



Peter Jausovec wrote:
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

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



Peter Jausovec wrote:
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

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

Thanks for the replies!

"Peter Jausovec" wrote:

Hi,

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

Hope this helps.

Peter
--
http://blog.jausovec.net


"RHmcse2003" je napisal:

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





--

Dave Peterson