vba for time stamp which wont update

i am creating a flight booking system

i need a vba which will put the time in column q when column o has been changed from live to cancelled

the sheet also has a sort flights into order button on previous attempts when this has been updated it changes all the times (i need the times to stay the same)

any help would be much appreciated

Hi steddas,

When you use formula's like TODAY() or NOW() then the date and time never get stored in a fixed moment. because they are formula's that will be recalculated every time the sheet is changed.
So indeed you need a small piece of VBA to take care of that. But there is no need to have separate columns for date and time. you can put all in one column and just change the date formatting of the cells to also include the time

I have made a change event macro that will put the date and time Cancelled in column P whenever in column O "cancelled" is chosen
the event macro can be found in the Sheet1 page of the VB Editor
now your timestamps should not change any more
if you would like to have separate columns for date and time it is possible.


______________-
also free extra tip if you want to put date or time in a cell manually quickly
use these shortcutkeys
for current date use CTRL + ;
for current time use CRTL + :

Hi Roel

thanks for the code you make it look so simple ive been getting stressed all day !!

i need the date in column p and the time in column q but i also need them do disappear if column o is put back to live is this possible ??

once again thank you for your help

Kind Regards
Steddas

Sure that is not a big ask.. just replace the previous code with this new one

Please Login or Register  to view this content.

the elseif statement will only remove the cancelled date and time if column O is set to "Live"
if for some reason the field is made blank the cancelled date and time should remain if it was ever there. (just a clarification of how the code responds)

Thank you so much this works so well

Kind Regards
Steddas

Hi Roel

i copied the new code into the the sheet and it works fine but when i protect the sheet the vba wont work because i need columns N, P, Q locked and when i protect the sheet i get runtime error 1004 i have attached the workbook and the passwork for unlocking is eglk

i also need it when live or cancelled is deleted it roves the contents from columns p, q

Thanks for your help i know im asking a lot here

Kind Regards
Steddas

sorry Roel here is the attachment

Ok no problem I added some code to handle the protection of the worksheet.

There are 2 ways to do it in the existing macro every time unprotect before starting the code and protect again at the end of the code.
But because it is an eventcode that would mean sheet gets unprotected many times whenever a change is made in column O. And if for some reason the code freezes the sheet might stay unprotected

So I prefer the other option that is to only apply the protection only to the Userinterface so it won't interfear with the macro's
for this I created a Workbook_open event macro that will set the protection of sheet1 every time you open the Workbook.

Please Login or Register  to view this content.

This way you can be assured that every time the workbook is open the users cannot manipulate the columns you want to protect
But it won't interfear with the macro's

You also can still do it yourself via the menu, but then you do cannot set the userinterface only option so if you protect a sheet manually the macros will give problems and you will need to close and reopen the workbook to let it function again. But I think after the workbook is finished there is almost never a need to remove protection.
Also when you want to change the password of the protection you must remember to do it in the workbook open code.

Hi Roel

this is brilliant thank you so much for your help

Hi Roel i just noticed a problem with the worksheet

because it protects the sheet every time i open the workbook now the slicers wont work is there any way the code can be altered to fix this ???

Kind Regards
Gary

Argh.. i often forget to check that kind of stuff whenever protecting sheets.

so now the code in workbook_open event (in thisWorkbook) also allows for using pivottables, autofilters and objects (for the slicers) and to only select the unlocked cells.

Maybe it needs a little more tweaking on what to allow or not, just let me know.

Thank you Roel you have been brilliant

Kind Regards

Hi Roel

ive been using the sheet you set the code up for i need 1 more favor in column N i need the date not to change every time the sheet is opened is this possible

Kind Regards
Steddas

Hi Steddas,

Find the code in attached sheet. It will fill a date if j thru m are not blank


Please also note that is not allowed to multipost the same question you now have asked the same question 3 times.. this is against the forum rules!

Hi Roel

That worked brilliantly you have been awsome

P.S sorry didnt know about the 3 question rule i will read up on the forum rules, once again you been brilliant i cant thank you enough