Lock cell after macro got runed

[Hudson]

Hi Forum contributors and experts .

Wondering if some one could help me in fixing minute thing that is result of human integrity LOL !!

coming to the point I prepared a template that calculates time laps between two given times and gives actual time taken for an activity . everything is going fine . but the problem is agents changing the time for their convenient .. it cant be that way ... columns (I , J ,K ) should be locked for editing or changing once macro got executed.


attached is the file for your reference .

[Kenneth Hobs]

1. Lock the columns.
2. Add modified code for ThisWorkbook and change ken to suit.
3. Add modified sheet1 code. Note how I removed option to select a row.


Sub workbook_open()
Sheet1.Protect "ken", UserInterfaceOnly:=True
Sheet1.StartCmd.Enabled = True
Sheet1.Stopcmd.Enabled = False
Columns("I:K").EntireColumn.Locked = True
End Sub

Dim Starttime As Date
Dim StopTime As Date
Dim RowNum As Long


Private Sub StartCmd_Click()
Starttime = Now
RowNum = Sheet1.Range("I" & Rows.Count).End(xlUp).Row + 1
Stopcmd.Enabled = True
StartCmd.Enabled = False
MsgBox "session time process is started successfully"
End Sub

[Hudson]

Hi ken ... thank you very much for coming back on my request ... IT IS GIVING ME compile error . expected start date ..

and is this a revised code ?. replaced with orginal one ?. little confused ... can advice please ...

[Kenneth Hobs]

Yes, just replace your code for those parts with mine.

I added a few tweaks to correct a posting issue and 2 lines of codes.

Obviously, you don't really need to lock those columns as you can do that manually just once but it hurts nothing.

[Hudson]

Ken , its a brilliant move ... i have checked it and this is what i was looking for ... ken - can you make below to be more dynamic .

i want below to be get executed using events .


Sub workbook_open() Sheet1.Protect "ken", UserInterfaceOnly:=True
Sheet1.StartCmd.Enabled = True
Sheet1.Stopcmd.Enabled = False
Columns("I:K").EntireColumn.Locked = TrueEnd Sub

[Kenneth Hobs]

What do you want to be dynamic? I don't see a need to let the user modify columns I:K. It already finds the next row to update. Some might use a column Change event to trigger starting the process. Of course once changed, that row should be locked. One would still need to click the end button. Look for Intersect() if that trigger interests you.

I would suggest removing the MsgBox(). You can use StatusPrompt() to inform the user if they care to know what is running or not. Those things should start before the time starts and end after time ends.

[Hudson]

ken- that's correct . I removed msg box . this is working fine but I unable to change or update the comments in " L " column . that is mandatory I need to put comments in column ( A, to H and L) except (I J K ). with the above program i was not able to update or change cells ,

can you advice please ..

[Kenneth Hobs]

Unless you locked those other cells, you can change those unprotected cells as you like.

Your macro can update other cells to. e.g. Column A, the date. I would probably lock the date cells though.

[Hudson]

Offcource . I am only locking i J k and what i see is everything in the sheet are getting locked .

[Kenneth Hobs]

Unlock the other columns manually and then run the macro. I would also manually lock the 3 columns and delete line from the macro.

The other cells if then locked after a macro run might be due to some other code that you have.

[Hudson]

thanks ken .. this is awesome ... thanks for your help .

[jwise]

One thing I've done in this situation is to set up modifiable cells with a particular background color. Then you call a sub that allows modification in cells that have this background color, It's nice for the user since it's easy to see where/what you can change.

[Kenneth Hobs]

I do the same.

I have used an off-yellow fill color with a bluish font color so that if they print black and white, it does not show the yellow and the blue as a lighter black color.

This shows how to use conditional format to format cell(s) if not locked. http://www.excel-university.com/format-locked-or-unlocked-cells/
Using this method, code would simply set the locked to true for those cells where you want to both lock/unlock and format accordingly.