| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use of NOW() function - followed by locking the cell
I have a table in Excel with a date/time column and a comment column.
I would like to select a cell in the date/time column and then push a button which enters the current date and time into that cell. I would then like to make it so that the cell is locked so that the the user cannot change the cell again without entering a password. The date/time would then be preserved next to when the comment is entered. Ideally once the date/time is entered, upon selecting the same cell again, the cell would call up the password dialog box to enter a password to unlock the cell so that a new date/time could be entered if desired. The code would be able to determine if the cell is locked for editing when it is selected. Sounds complicated... Can anyone help? Best regards, Roger |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use of NOW() function - followed by locking the cell
Hi Roger
Look at the code below, just remember that you have to unlock all cells which user should be allowed to cange. Sub TimeStamp() Dim TimeCol As String Dim shtpWord As String Dim AllowChangepWord As String Dim pWord As String TimeCol = "A" ' Change to suit shtpWord = "JustMe" AllowChangepWord = "AllowChange" Set isect = Intersect(ActiveCell, Columns(TimeCol)) If Not isect Is Nothing Then If ActiveCell.Locked = False Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord Else pWord = InputBox("Enter password to change date/Time in cell") If pWord = AllowChangepWord Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord End If End If End If End Sub Regards, Per "Roger on Excel" skrev i meddelelsen ... I have a table in Excel with a date/time column and a comment column. I would like to select a cell in the date/time column and then push a button which enters the current date and time into that cell. I would then like to make it so that the cell is locked so that the the user cannot change the cell again without entering a password. The date/time would then be preserved next to when the comment is entered. Ideally once the date/time is entered, upon selecting the same cell again, the cell would call up the password dialog box to enter a password to unlock the cell so that a new date/time could be entered if desired. The code would be able to determine if the cell is locked for editing when it is selected. Sounds complicated... Can anyone help? Best regards, Roger |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use of NOW() function - followed by locking the cell
Dear Per,
Thanks for the code. Where should I paste it - i tried to paste it in a module without success. Regards, Roger "Per Jessen" wrote: Hi Roger Look at the code below, just remember that you have to unlock all cells which user should be allowed to cange. Sub TimeStamp() Dim TimeCol As String Dim shtpWord As String Dim AllowChangepWord As String Dim pWord As String TimeCol = "A" ' Change to suit shtpWord = "JustMe" AllowChangepWord = "AllowChange" Set isect = Intersect(ActiveCell, Columns(TimeCol)) If Not isect Is Nothing Then If ActiveCell.Locked = False Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord Else pWord = InputBox("Enter password to change date/Time in cell") If pWord = AllowChangepWord Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord End If End If End If End Sub Regards, Per "Roger on Excel" skrev i meddelelsen ... I have a table in Excel with a date/time column and a comment column. I would like to select a cell in the date/time column and then push a button which enters the current date and time into that cell. I would then like to make it so that the cell is locked so that the the user cannot change the cell again without entering a password. The date/time would then be preserved next to when the comment is entered. Ideally once the date/time is entered, upon selecting the same cell again, the cell would call up the password dialog box to enter a password to unlock the cell so that a new date/time could be entered if desired. The code would be able to determine if the cell is locked for editing when it is selected. Sounds complicated... Can anyone help? Best regards, Roger |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use of NOW() function - followed by locking the cell
Dear Roger,
Paste it in a ordinary module, the insert a Button from the Control Toolbox menu (ActiveX control if you are using excel 2007). Then right click the button and select 'View Code'. Between 'Private Sub...' and 'End Sub' insert 'Call TimeStamp'. Return to the worksheet and exit Design mode. Hopes this helps. Per "Roger on Excel" skrev i meddelelsen ... Dear Per, Thanks for the code. Where should I paste it - i tried to paste it in a module without success. Regards, Roger "Per Jessen" wrote: Hi Roger Look at the code below, just remember that you have to unlock all cells which user should be allowed to cange. Sub TimeStamp() Dim TimeCol As String Dim shtpWord As String Dim AllowChangepWord As String Dim pWord As String TimeCol = "A" ' Change to suit shtpWord = "JustMe" AllowChangepWord = "AllowChange" Set isect = Intersect(ActiveCell, Columns(TimeCol)) If Not isect Is Nothing Then If ActiveCell.Locked = False Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord Else pWord = InputBox("Enter password to change date/Time in cell") If pWord = AllowChangepWord Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord End If End If End If End Sub Regards, Per "Roger on Excel" skrev i meddelelsen ... I have a table in Excel with a date/time column and a comment column. I would like to select a cell in the date/time column and then push a button which enters the current date and time into that cell. I would then like to make it so that the cell is locked so that the the user cannot change the cell again without entering a password. The date/time would then be preserved next to when the comment is entered. Ideally once the date/time is entered, upon selecting the same cell again, the cell would call up the password dialog box to enter a password to unlock the cell so that a new date/time could be entered if desired. The code would be able to determine if the cell is locked for editing when it is selected. Sounds complicated... Can anyone help? Best regards, Roger |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Use of NOW() function - followed by locking the cell
Thanks Per,
All the best, Roger "Per Jessen" wrote: Dear Roger, Paste it in a ordinary module, the insert a Button from the Control Toolbox menu (ActiveX control if you are using excel 2007). Then right click the button and select 'View Code'. Between 'Private Sub...' and 'End Sub' insert 'Call TimeStamp'. Return to the worksheet and exit Design mode. Hopes this helps. Per "Roger on Excel" skrev i meddelelsen ... Dear Per, Thanks for the code. Where should I paste it - i tried to paste it in a module without success. Regards, Roger "Per Jessen" wrote: Hi Roger Look at the code below, just remember that you have to unlock all cells which user should be allowed to cange. Sub TimeStamp() Dim TimeCol As String Dim shtpWord As String Dim AllowChangepWord As String Dim pWord As String TimeCol = "A" ' Change to suit shtpWord = "JustMe" AllowChangepWord = "AllowChange" Set isect = Intersect(ActiveCell, Columns(TimeCol)) If Not isect Is Nothing Then If ActiveCell.Locked = False Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord Else pWord = InputBox("Enter password to change date/Time in cell") If pWord = AllowChangepWord Then ActiveSheet.Unprotect Password:=shtpWord ActiveCell = Now() ActiveCell.Locked = True ActiveSheet.Protect Password:=shtpWord End If End If End If End Sub Regards, Per "Roger on Excel" skrev i meddelelsen ... I have a table in Excel with a date/time column and a comment column. I would like to select a cell in the date/time column and then push a button which enters the current date and time into that cell. I would then like to make it so that the cell is locked so that the the user cannot change the cell again without entering a password. The date/time would then be preserved next to when the comment is entered. Ideally once the date/time is entered, upon selecting the same cell again, the cell would call up the password dialog box to enter a password to unlock the cell so that a new date/time could be entered if desired. The code would be able to determine if the cell is locked for editing when it is selected. Sounds complicated... Can anyone help? Best regards, Roger |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Locking from data entry but allowing oulining to function? | Excel Discussion (Misc queries) | |||
| Locking the macro view function | Excel Discussion (Misc queries) | |||
| Locking cells but keeping Grouping Function available | Excel Worksheet Functions | |||
| locking formula in cells in without locking whole sheet | Excel Discussion (Misc queries) | |||
| Excel 2000 - Function call causes Locking up/Crashing | Excel Programming | |||
Hybrid Mode
