+ Reply to Thread
Results 1 to 4 of 4

Macro into locked cells

  1. #1
    Registered User
    Join Date
    11-29-2006
    Posts
    2

    Macro into locked cells

    im making a sheet where employees, after making a phonecall, hit a macro button to date/time stamp a cell. The problem being that we want these cells locked so after using the timestamp macro the employees cannot go in and alter the times.

    the current macro is very simple-
    //
    Sub NOWTIME()
    ActiveCell.Value = Format(Now(), "MM.dd h:mm AM/PM")
    End Sub
    //

    If anyone could help it would be greatly appreciated.

    currently the cells are locked and i have already passworded them

  2. #2
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    You can protect and unprotect the sheet in VBA with

    ActiveSheet.Protect Password:="myPassword"
    ActiveSheet.Unprotect Password:="myPassword"

    You should then probably put a password on the VBA code to stop them looking at the password

  3. #3
    Registered User
    Join Date
    11-29-2006
    Posts
    2

    okay, one more question

    the one problem that i have run into is that now they still have the ability to over-write the protected cells by just re-runnign the macro.. is there any way for the macro to only write to the cell if its empty?

    current macro code:

    Sub NOWTIME()
    ActiveSheet.Unprotect Password:="pw"
    ActiveCell.Value = Format(Now(), "MM.dd h:mm AM/PM")
    ActiveSheet.Protect Password:="pw"
    End Sub

  4. #4
    Valued Forum Contributor
    Join Date
    06-16-2006
    Location
    Sydney, Australia
    MS-Off Ver
    2013 64bit
    Posts
    1,394
    sure, just put a test at the start of the macro

    Sub NOWTIME()
    if activecell.value= "" then
    ActiveSheet.Unprotect Password:="pw"
    ActiveCell.Value = Format(Now(), "MM.dd h:mm AM/PM")
    ActiveSheet.Protect Password:="pw"
    end if
    End Sub

+ 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