+ Reply to Thread
Results 1 to 18 of 18

How do I protect only formulas in Excel?

  1. #1
    RHmcse2003
    Guest

    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

  2. #2
    Peter Jausovec
    Guest

    RE: How do I protect only formulas in Excel?

    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


  3. #3
    Kassie
    Guest

    RE: How do I protect only formulas in Excel?

    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


  4. #4
    RHmcse2003
    Guest

    RE: How do I protect only formulas in Excel?

    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


  5. #5
    Peter Jausovec
    Guest

    RE: How do I protect only formulas in Excel?

    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


  6. #6
    b&s
    Guest

    Re: How do I protect only formulas in Excel?

    .... 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 orevfyni.frire@mt.g-pbz.ue



    Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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





  7. #7
    Peter Jausovec
    Guest

    Re: How do I protect only formulas in Excel?

    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 orevfyni.frire@mt.g-pbz.ue
    >
    >
    >
    > Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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

    >
    >
    >
    >


  8. #8
    b&s
    Guest

    Re: How do I protect only formulas in Excel?

    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 orevfyni.frire@mt.g-pbz.ue



    Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>
    >>
    >>
    >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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





  9. #9
    Peter Jausovec
    Guest

    Re: How do I protect only formulas in Excel?

    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 orevfyni.frire@mt.g-pbz.ue
    >
    >
    >
    > Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    > >>
    > >>
    > >>
    > >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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

    >
    >
    >
    >


  10. #10
    b&s
    Guest

    Re: How do I protect only formulas in Excel?

    .... 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 <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>
    >>
    >>
    >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>>>
    >>>>
    >>>>
    >>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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






  11. #11
    Dave Peterson
    Guest

    Re: How do I protect only formulas in Excel?

    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 orevfyni.frire@mt.g-pbz.ue
    > >
    > >
    > >
    > > Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    > > >>
    > > >>
    > > >>
    > > >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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

  12. #12
    b&s
    Guest

    Re: How do I protect only formulas in Excel?

    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 <ec35720@netscapeXSPAM.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>>
    >>>
    >>>
    >>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>>>>
    >>>>>
    >>>>>
    >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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








  13. #13
    Peter Jausovec
    Guest

    Re: How do I protect only formulas in Excel?

    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 <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    > >>
    > >>
    > >>
    > >> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    > >>>>
    > >>>>
    > >>>>
    > >>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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

    >
    >
    >
    >
    >


  14. #14
    b&s
    Guest

    Re: How do I protect only formulas in Excel?

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


    --
    pozdrav!
    Berislav

    Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>>>
    >>>>
    >>>>
    >>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>>>>>
    >>>>>>
    >>>>>>
    >>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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






  15. #15
    Dave Peterson
    Guest

    Re: How do I protect only formulas in Excel?

    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 <ec35720@netscapeXSPAM.com> 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 orevfyni.frire@mt.g-pbz.ue
    > >>>
    > >>>
    > >>>
    > >>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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

  16. #16
    b&s
    Guest

    Re: How do I protect only formulas in Excel?

    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 <ec35720@netscapeXSPAM.com> 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 <ec35720@netscapeXSPAM.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>>>>
    >>>>>
    >>>>>
    >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    >>>>>>>
    >>>>>>>
    >>>>>>>
    >>>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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





  17. #17
    RHmcse2003
    Guest

    Re: How do I protect only formulas in Excel?

    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 <ec35720@netscapeXSPAM.com> 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 <ec35720@netscapeXSPAM.com> 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 orevfyni.frire@mt.g-pbz.ue
    > >>>>>
    > >>>>>
    > >>>>>
    > >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    > >>>>>>>
    > >>>>>>>
    > >>>>>>>
    > >>>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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

    >
    >
    >
    >


  18. #18
    Dave Peterson
    Guest

    Re: How do I protect only formulas in Excel?

    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 <ec35720@netscapeXSPAM.com> 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 <ec35720@netscapeXSPAM.com> 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 orevfyni.frire@mt.g-pbz.ue
    > > >>>>>
    > > >>>>>
    > > >>>>>
    > > >>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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 orevfyni.frire@mt.g-pbz.ue
    > > >>>>>>>
    > > >>>>>>>
    > > >>>>>>>
    > > >>>>>>> Peter Jausovec <PeterJausovec@discussions.microsoft.com> 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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1