My VBA Timestamp(X) function stop working when I add a tab to my workbook

Hi,

I am using this VBA function in my module 1 in the VBA :

Please Login or Register  to view this content.

So basically when someone enters a value in certain cell, the date is automatically written in a cell next to it.

Everything works fine until I add a new tab, then, every date in my workbook is updated to the current date and I can't seem to correct this problem.

Does anyone have an idea on how to solve this ?

Best regards,

So basically when someone enters a value in certain cell

What certain cell...Same cells in all sheets...Only certain sheets????
I would change this to a Workbook Sheet_Change Event

I am not sure that I understand correctly but as Now is volatile it is obvious.

As per post 2

What certain cell...Same cells in all sheets...Only certain sheets????

Here is an example...Will give you an idea...
Place this in ThisWorkBook Module
When you enter anything in Range("A1:A20") of any sheet, the date will be entered into cell next to it...even when you add new tabs...

Please Login or Register  to view this content.

Make it easier for us...
Your post 1 says...

So basically when someone enters a value in certain cell, the date is automatically written in a cell next to it.

What cells in which tabs do you enter data into that has to have the cell next to it populated with the Date

This will give you an idea...Have only coded for B1 & B4

I removed your Function...

This seems less versatile and more complicated for the not so different result.

Most certainly not...Welcome to the world of VBA...Gone are the days where you need to populate thousands of rows with formulas and functions for possible entries...

With Function you are forced to enter the function formula manually in every cell etc etc etc
With below code it all happens automatically as you enter values into the given ranges...

This code below solves all required...

-Write in B1, date in B2
-Write in B4, date in B5
-Write in GX, date in NX
-Write in HX, IX, Jx or KX, date in OX

Please Login or Register  to view this content.

Try the file attached and see what I mean...

Using my file I attached...

Can we change it so the date updates If I remodify the cell the day after ?

The code already does that...

Can we make it so if I erase the content of the cell, the date will disappear ?

Red snippets amended...

I could use "Today" instead on "Now"

Yes...

Please Login or Register  to view this content.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. 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, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, 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

(Note: this change is not optional. As you are still new here, I have done it for you this time.)

Does work for B1 and B4

Untitled.png

Get rid of your merged cells in rows...1 to 5...
Excel's rule No 1 do not use merged cells...Causes havoc with VBA
This snippet here bypasses the code if you want to clear more than one cell

Please Login or Register  to view this content.

With merged cells in those rows the count = 4 hence code is bypassed...

Here is an alternative for Merged Cells and I have updated C1 accordingly which allows code to work perfectly...

Pleasure...See post 18 update...

there a way with my old version of the workbook to add tabs without losing my time stamps

I suggest updating your file with the new code and replace all previously entered time stamps formula with values...

It is only range("B2,B5,N:O") of C# sheets...not so...

Something like this...Then delete your function...Then add my code I supplied...This will then work all round....

Please Login or Register  to view this content.

If you don't come right upload your file and I will update it quickly...
Please do replace those merged cells though...

If it worked before then it should still work...Just tested ... All works as it should...
remember to unprotect sheets!

2. I tried your code for the already existing workbooks and it didnt work

If you follow the steps above it should work perfectly...Cannot do anything to assist without seeing said workbook...

Is there a way to make it work while protecting the sheets ?

Place this in the sheet module of sheets...

Please Login or Register  to view this content.

Just check if enableevents is true...Place this in a module and run

Please Login or Register  to view this content.

Should be True

I am assuming that an error occurred when trying to run code as the sheets were protected...
This caused the code when firing not to complete...which did not turn on Enable Events
Hence the code was not firing...
I just turned on this event so that the code could fire...
Change your workbook code to...

Please Login or Register  to view this content.

Also, why did you add this in the module 4 ?

Please Login or Register  to view this content.

Was just turning on the event

Most certainly not...Is as my Signature in Post 2 says...

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also...As you are fairly new here, you might not know that you can thank those who have helped by adding to their rep...Left of post...
[* Add Reputation]