+ Reply to Thread
Results 1 to 6 of 6

Timestamp column protection - whilst allowing for form to enter new records in rows

  1. #1
    Registered User
    Join Date
    09-06-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    69

    Timestamp column protection - whilst allowing for form to enter new records in rows

    Hi,

    I have a form which sends info to a spreadsheet, including a timestamp appearing in column B (please see image file attached)

    I would like a timestamp to appear in column C whenever changes are made to the spreadsheet for a given row, and then have the timestamps in column B and C protected from manual editing, although I would like columns D to Z to be allowed to be edited directly from the spreadsheet.

    I have tried solutions provided to other users on this forum but they have not worked, such as:

    https://www.mrexcel.com/forum/excel-...ime-stamp.html

    Please Login or Register  to view this content.
    Attached Images Attached Images
    Last edited by excelconditional; 02-26-2017 at 06:42 PM. Reason: Code tags

  2. #2
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Timestamp column protection - whilst allowing for form to enter new records in rows

    We get in trouble if we answer your question if the code does not have CODE TAGS.

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

  3. #3
    Registered User
    Join Date
    09-06-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    69

    Re: Timestamp column protection - whilst allowing for form to enter new records in rows

    Hi LJ, I have added the code tags - apologies...

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Timestamp column protection - whilst allowing for form to enter new records in rows

    Thanks for adding the Code Tags.

    I would like a timestamp to appear in column C whenever changes are made to the spreadsheet for a given row, and then have the timestamps in column B and C protected from manual editing, although I would like columns D to Z to be allowed to be edited directly from the spreadsheet.
    There are a couple of issues here in addition to your question:
    a. Your macro only allows a change in one cell at a time. Multiple cells can be changed (e.g. cut and paste) and cells will change but there will be no update.
    b. The Sheet has to be initialized to prepare for your spreadsheet changes, otherwise the cells you want to change will probably be locked (as cells are usually initialized to the 'locked' state):

    To initialize the sheet either manually or with the following code, to Unlock all cells except columns 'B' and 'C'. In an ordinary code module such as Module1:
    Please Login or Register  to view this content.
    Your code was close but I think you got confused with
    Please Login or Register  to view this content.
    which says Put the date in the cell 3 rows down from the cell that changed value. You really want to just change Column 'C'. Try the following:
    Please Login or Register  to view this content.
    I hope this helps.

    Lewis

  5. #5
    Registered User
    Join Date
    09-06-2016
    Location
    UK
    MS-Off Ver
    2016
    Posts
    69

    Re: Timestamp column protection - whilst allowing for form to enter new records in rows

    Hi LJ,

    Thanks for the correction.

    Your code allows me to protect the sheet once an entry is submitted via the form. But I get this error when I try to add the data to the spreadsheet:


    fff.JPG

    Also, my original timestamp (in Column B) disapears completely... but Column G the employee number column, ends up displaying what appears to be a date, but with the date and time 1 day prior. All the other columns appear to receive information in the correct place, however (see screenshot below)..

    error.JPG

    I think you have got it almost working, but there are these slight issues..
    Last edited by excelconditional; 02-26-2017 at 10:26 PM. Reason: addition

  6. #6
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Timestamp column protection - whilst allowing for form to enter new records in rows

    Hi,

    Your problems are typical of problems that occur with 'Protected Worksheets'. The 1004 error is probably occurring because you are trying to change the contents of a 'Locked' cell on a 'Protected' Worksheet. Since you are using a UserForm, you probably need to add a couple of lines in the 'Ammend' and 'Add new record' CommandButton Event handler code to unprotect the sheet while writing to the Spreadsheet. For example:
    Please Login or Register  to view this content.
    my original timestamp (in Column B) disappears completely... but Column G the employee number column, ends up displaying what appears to be a date, but with the date and time 1 day prior.
    Without a sample workbook, I can only suggest methods to diagnose your problems. Obviously, you are probably writing BLANK data to Column 'B' and writing something to Column 'G' both of which you don't want.

    I suggest you do the following:
    a. Before trying to diagnose the problem, make sure you have the correct code to write to Column 'C'. Write a test macro something like the following to make sure you have code that does what you want:
    Please Login or Register  to view this content.
    b. Next, find where you are going wrong. To start with, it is a lot easier to debug UserForm code when the UserForm is Modeless which allows access to Excel Resources. When you open the UserForm use code like:
    Please Login or Register  to view this content.
    When your code is finished you can remove the vbModeless to make the Userform Modal, which locks out Excel Resources.

    c. Put a breakpoint at the beginning of the UserForm 'Ammend' and/or 'Add new record' CommandButton Event handler code and single step through the code. Each time you single step take a look at the spreadsheet to make sure the action you thought you requested, actually occurred.

    Debugger Secrets:
    a. Press 'F8' to single step (goes into subroutines and functions).
    b. Press SHIFT 'F8' to single step OVER subroutines and functions.
    c. Press CTRL 'F8' to stop at the line where the cursor is.
    d. 'Left Click' the margin to the left of a line to set (or clear) a BREAKPOINT.
    e. Press CTRL 'G' to open the IMMEDIATE WINDOW. 'debug.print' statements send their
    output to the IMMEDIATE WINDOW.
    f. Select View > Locals to see all variables while debugging.
    g. To automatically set a BREAKPOINT at a certain location put in the line:
    'Debug.Assert False'
    h. To conditionally set a BREAKPOINT at a certain location put in lines similar to:
    if i >= 20 and xTV20 > 99.56 then
    Debug.Assert False
    endif
    i. A variable value will be displayed by putting the cursor over the variable name.

    To manually set a breakpoint, see http://www.wiseowl.co.uk/blog/s196/breakpoints.htm

    To upload a sample workbook: Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adding worksheet protection but allowing form controls, macros
    By HeyInKy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-05-2014, 01:27 PM
  2. How to enter records using a form?
    By Bill_Holland in forum Excel General
    Replies: 2
    Last Post: 02-24-2013, 02:59 PM
  3. User Form VBA coding to enter, edit and add new records
    By SGS in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2012, 03:59 AM
  4. Protecting cells whilst allowing hyperlinks to be used
    By Ben.SFM in forum Excel General
    Replies: 0
    Last Post: 12-01-2011, 12:09 PM
  5. Replies: 2
    Last Post: 08-22-2006, 10:05 AM
  6. [SOLVED] Protection from format changes allowing form to be filled in
    By Clare in forum Excel General
    Replies: 1
    Last Post: 10-21-2005, 02:05 PM
  7. [SOLVED] Enter timestamp when column is modified
    By Mikus in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-04-2005, 01:05 AM

Tags for this Thread

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