+ Reply to Thread
Results 1 to 8 of 8

Auto Protecting cells & auto filling date

  1. #1
    Registered User
    Join Date
    09-29-2005
    Posts
    11

    Auto Protecting cells & auto filling date

    I wonder if anyone can tell me if it is possible to auto proect cells after data has been entered?

    Basically I have a spreadsheet that has two levels of protection - some cells are password protected and others available for all to enter data into.

    What I would like to achieve is once data has been entered into a row in the editable area that row becomes part of the password protected area automatically.

    Also is it possible to use the NOW (or similar) function to populate one cell with a date when another is filled?

    Ie a log entry is made in column B. When some data is entered into column B cthe corresponding entry in column A gets filled with todays date.

    Any help much appreciated.

    Regards
    Clive

  2. #2
    widman
    Guest

    RE: Auto Protecting cells & auto filling date

    I can't answer the first part, but the second part is easy
    just enter into a1 =IF(b1=0,0,NOW())
    format a1 as a date
    in tools/options uncheck show zeros

    "ccarmock" wrote:

    >
    > I wonder if anyone can tell me if it is possible to auto proect cells
    > after data has been entered?
    >
    > Basically I have a spreadsheet that has two levels of protection - some
    > cells are password protected and others available for all to enter data
    > into.
    >
    > What I would like to achieve is once data has been entered into a row
    > in the editable area that row becomes part of the password protected
    > area automatically.
    >
    > Also is it possible to use the NOW (or similar) function to populate
    > one cell with a date when another is filled?
    >
    > Ie a log entry is made in column B. When some data is entered into
    > column B cthe corresponding entry in column A gets filled with todays
    > date.
    >
    > Any help much appreciated.
    >
    > Regards
    > Clive
    >
    >
    > --
    > ccarmock
    > ------------------------------------------------------------------------
    > ccarmock's Profile: http://www.excelforum.com/member.php...o&userid=27670
    > View this thread: http://www.excelforum.com/showthread...hreadid=471820
    >
    >


  3. #3
    widman
    Guest

    RE: Auto Protecting cells & auto filling date

    The only problem with NOW is that it always will reset to the day it is
    opened or printed

    "widman" wrote:

    > I can't answer the first part, but the second part is easy
    > just enter into a1 =IF(b1=0,0,NOW())
    > format a1 as a date
    > in tools/options uncheck show zeros
    >
    > "ccarmock" wrote:
    >
    > >
    > > I wonder if anyone can tell me if it is possible to auto proect cells
    > > after data has been entered?
    > >
    > > Basically I have a spreadsheet that has two levels of protection - some
    > > cells are password protected and others available for all to enter data
    > > into.
    > >
    > > What I would like to achieve is once data has been entered into a row
    > > in the editable area that row becomes part of the password protected
    > > area automatically.
    > >
    > > Also is it possible to use the NOW (or similar) function to populate
    > > one cell with a date when another is filled?
    > >
    > > Ie a log entry is made in column B. When some data is entered into
    > > column B cthe corresponding entry in column A gets filled with todays
    > > date.
    > >
    > > Any help much appreciated.
    > >
    > > Regards
    > > Clive
    > >
    > >
    > > --
    > > ccarmock
    > > ------------------------------------------------------------------------
    > > ccarmock's Profile: http://www.excelforum.com/member.php...o&userid=27670
    > > View this thread: http://www.excelforum.com/showthread...hreadid=471820
    > >
    > >


  4. #4
    Registered User
    Join Date
    09-29-2005
    Posts
    11
    Yes I see what you mean - so every time I open the sheet the dates are updated.....

    If the first time it triggers it coudl replace the formula with the actual value that woudl achieve what I need....

  5. #5
    Gord Dibben
    Guest

    Re: Auto Protecting cells & auto filling date

    ccarmock

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'when entering data in a cell in Col B
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 2 Then
    n = Target.Row
    If Excel.Range("B" & n).Value <> "" Then
    Excel.Range("A" & n).Value = Now
    End If
    End If
    enditall:
    Application.EnableEvents = True
    End Sub

    This is sheet event code.

    Right-click on the sheet tab and "View Code".

    Copy/paste the code into that module. As you enter data in column B, the date
    will be placed in A on same row.


    Gord Dibben Excel MVP

    On Thu, 29 Sep 2005 14:38:30 -0500, ccarmock
    <ccarmock.1w4zie_1128024309.0695@excelforum-nospam.com> wrote:

    >
    >Yes I see what you mean - so every time I open the sheet the dates are
    >updated.....
    >
    >If the first time it triggers it coudl replace the formula with the
    >actual value that woudl achieve what I need....



  6. #6
    Registered User
    Join Date
    09-29-2005
    Posts
    11
    Hi - Many thanks that works perfectly!

    I wonder if something similar could be used to modify the ranges used to protect data in the worksheet?

    Ie once data is entered into a cell in column B I woudl then like to change that cell from being in a range that allows anyone to edit into a range that is password protected.

    Regards
    Clive

  7. #7
    Registered User
    Join Date
    09-29-2005
    Posts
    11
    One further question on this - when I lock the spreadsheet (as I dont want the date to be modified by the user, the system can no longer update column A with the date of the change.

    To achieve this I have column B set to allow Everyone to make changes while the rest of the sheet requires a password to edit ranges.

    I guess I can get the Macro to turn off protection and turn it on again? However would this mean storing the password in the Macro? Again not an issue, but I would then need to be able to prevent the user from looking at the Macro code to discover the password - is that possible?

    Or alternatively is there the concept of allowing the 'SYSTEM' permission to update a range of cells, this might be a cleaner approach.

  8. #8
    Gord Dibben
    Guest

    Re: Auto Protecting cells & auto filling date

    ccarmock

    Depending upon how sophisticated your users are and whether or not they enable
    macros upon opening the workbook you can achieve most of what you want.

    First, unlock columns A and B then protect the sheet with a password("justme")
    is example only.

    The Sub following will enter a date in column A and lock that cell in Column A
    but leaving rest of column A unlocked.

    B must remain unprotected in order for users to enter data.

    Add the code below to the sheet module.

    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    'when entering data in a cell in Col B
    'if a date is in A, it won't change when B is updated
    On Error GoTo enditall
    Application.EnableEvents = False
    If Target.Cells.Column = 2 Then
    SHEETUNPROTECT
    N = Target.Row
    If Excel.Range("B" & N).Value <> "" _
    And Excel.Range("A" & N).Value = "" Then
    Excel.Range("A" & N).Value = Now
    Excel.Range("A" & N).Locked = True
    End If
    End If
    enditall:
    Application.EnableEvents = True
    SHEETPROTECT
    End Sub

    Now copy the following macros to a new general module in the workbook.

    Sub SHEETPROTECT()
    ActiveSheet.Protect Password:="justme", DrawingObjects:=True, _
    Contents:=True, Scenarios:=True
    End Sub

    Sub SHEETUNPROTECT()
    ActiveSheet.Unprotect Password:="justme"
    End Sub

    Right-click on the workbook/project and select VBAProject Properties then
    "Lock project for viewing"

    You can password protect this module so prying eyes can't see it and obtain
    the password("justme").

    You now save the workbook and close Excel....the VBAProject locking won't come
    into effect until Excel is closed out fully.

    If the users enable macros and if no one of them knows how to crack a
    VBAProject password, you should be good to go.


    Gord


    On Fri, 30 Sep 2005 12:30:06 -0500, ccarmock
    <ccarmock.1w6omi_1128103506.7565@excelforum-nospam.com> wrote:

    >
    >One further question on this - when I lock the spreadsheet (as I dont
    >want the date to be modified by the user, the system can no longer
    >update column A with the date of the change.
    >
    >To achieve this I have column B set to allow Everyone to make changes
    >while the rest of the sheet requires a password to edit ranges.
    >
    >I guess I can get the Macro to turn off protection and turn it on
    >again? However would this mean storing the password in the Macro?
    >Again not an issue, but I would then need to be able to prevent the
    >user from looking at the Macro code to discover the password - is that
    >possible?
    >
    >Or alternatively is there the concept of allowing the 'SYSTEM'
    >permission to update a range of cells, this might be a cleaner
    >approach.



+ 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