+ Reply to Thread
Results 1 to 2 of 2

Copy the formula from the protected cells

  1. #1
    AccessHelp
    Guest

    Copy the formula from the protected cells

    Hello all,

    I have an Excel file that the users input the information. The sheet in the
    file contains 10 columns (A-J), and the columns B,C,D and G are protected and
    formula-driven. The way I have setup is the users can insert a new row
    between two rows. The problem I am having is since some of the columns are
    protected, the formula in those cells are not copied to the protected new
    cells on the new row.

    Is there a code that does the copying of the formula from the above
    protected cell? Other suggestions are welcomed.

    Thanks.

  2. #2
    Norman Jones
    Guest

    Re: Copy the formula from the protected cells

    Hi AccessHelp,

    See David McRitchie's InsertRowsAndFillFormulas sub at:

    http://www.mvps.org/dmcritchie/excel/insrtrow.htm

    To deal wirh the protection issue, you could assign the macro to a button
    and either:

    (1) Turn protection off at the start of the macro and restore protection at
    the end, or

    (2) Setting the Protect method's UserInterfaceOnly parameter to true enables
    VBA manipulation of the protected sheet. However, this setting is not
    persistent and needs to be reset each time the workbook is opened.

    Perhaps, therefore, you could set protection in the Workbook_Open or
    Auto_Open procedures, e.g.:

    '============>>
    Sub Auto_Open()
    With Worksheets("sheet1")
    .Protect Password:="drowssap", UserInterfaceOnly:=True
    End With
    End Sub
    '<<============

    ---
    Regards,
    Norman



    "AccessHelp" <AccessHelp@discussions.microsoft.com> wrote in message
    news:35C7C9C4-0DB6-4F01-9DBA-CCA2B5F80168@microsoft.com...
    > Hello all,
    >
    > I have an Excel file that the users input the information. The sheet in
    > the
    > file contains 10 columns (A-J), and the columns B,C,D and G are protected
    > and
    > formula-driven. The way I have setup is the users can insert a new row
    > between two rows. The problem I am having is since some of the columns
    > are
    > protected, the formula in those cells are not copied to the protected new
    > cells on the new row.
    >
    > Is there a code that does the copying of the formula from the above
    > protected cell? Other suggestions are welcomed.
    >
    > Thanks.




+ 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