Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Require Coding for Automatic Timestamp in Module

0

Hi,

With refer to https://www.youtube.com/watch?v=gIPdx4-rqGg&t=902s

I want to write this below code in module.
But I see error of "424 Object required" when copied the code and run in Module.
Hence, Can you please help me with the correct coding or any changes required for the same 
Private Sub Worksheet_Change(ByVal Target As Range)   Dim myTableRange As Range Dim myDateTImeRange As Range Dim myUpdatedRange As Range   Set myTableRange = Range ("A2:D10")   If Intersect(Target, myTableRange) Is Nothing Then Exit Sub   Set myDateTimeRange = Range("E" & Target.Row) Set myUpdatedRange = Range ("F" & Target.Row)   If myDateTimeRange.Value = "" Then   myDateTimeRange.Value = Now   End If   myUpdatedRange.Value = Now   End Sub
Regards,
Nitil

Hi John,

Thanks for your quick response.

I wanted my workbook to give results like attached sheet," Run 2 Macros at Set Intervals v0 c".

Where, if we run the macro in sheet 1 and than after sometime if we change the sheet to sheet 2, than also the macro is running in sheet1 and giving result  like,

if when i start the macro in sheet 1, it start giving results like 1, 2, 3 and when i come to sheet 2 than after one minute or so, if i go to sheet 1, than i see the macro is continuously run in background and see the results like 8, 9,10, etc. means the macro  had not stopped when i change the sheet1 to sheet2.

Further, I am finding the problem in my sheet, that if i run the macro in sheet1 than when i go to sheet2, than the macro stop running means i have to keep sheet 1 active for the result.

I have attached my sample excel sheet where i require the same result like in attached sheet, "Run 2 Macros at Set Intervals v0 c" where if I run the macro in sheet1, and if i change the sheet from sheet 1 to sheet 2, the macro should run continuously in the sheet .

I want below code to run in module for the same.

Sub Worksheet_Change()

Dim myTableRange As Range

Dim myDateTimeRange As Range

Dim myUpdatedRange As Range

Sheet23.Range("B2:AP200") = Sheet23.Range("B2:AP200")

Set myTableRange = Range("AH2:AN300")

"If Intersect(Target, myTableRange) Is Nothing Then Exit Sub"

Set myDateTimeRange = Range("AO" & Target.Row)

Set myUpdatedRange = Range("AP" & Target.Row)

If myDateTimeRange.Value = "" Then

   myDateTimeRange.Value = Now

End If

myUpdatedRange.Value = Now

Call macro_timer

End Sub

Request you to Please help me with the solution.

Thank you & Best Regards,

Nitil

Revision 1

Hi,

Please find two attachments:

1. Run 2 Macros at Set Intervals v0 c

2. My sample Test excel sheet

In "Run 2 Macros at Set Intervals v0 c",  Whenever I run Macros in the sheet1, it working fine. Also if whenever i shift the sheet from sheet 1 to sheet 2, than also the macros is continuously running and updating the data.

In "My sample Test excel sheet", I am facing issue: Whenever I run the macros, its working, but i have to keep the same sheet active, but whenever i shift the sheet from sheet 1 to sheet 2, than the macros stops running in the required sheet and start running in sheet 2.

I wanted the macros to give me continuous results in sheet1 inspite of shift from sheet 1 to sheet 2.

I wanted result same result lik "Run 2 Macros at Set Intervals v0 c" in My sample Test excel sheet.

Further, My requirement is that the sheet1 is formula based sheet which is linked to sheet 2 which have data base. So, whenever there is updation in sheet 2, than sheet 1 is automatically updated, but i want to get notified whenever there is any change in sheet 1.

Secondly, due the formula based sheet1, there should be any change to the sheet to get notified.

Hence,  i have taken the timestamp and timer to get updated.

Hope you understand my requirement and would request you to please help me for the solution.

Thanks,

Nitil

Revision 2

Hi,

Please find my latest file i.e. "My sample Test excel sheet" which help you understand what i am looking for.

> Sheet 1 is updated through formula from sheet 2

I think there is no vba code for tracking formula based sheet. Hence, I have wrote VBA of macro for column D which update every interval i.e 1 & 2 and timestamp to capture the changes in sheet 1.

I wanted to be notified when there is any change in sheet 1. Hence, i have created pivot table for the same.

Here, my worry is when the macros is running and whenever i shift to any other sheet, the macros overwrite in other sheet which i have shifted to.

I wanted the macros to run to specific sheet rather any sheet which is shifted to view.

Please help me to limit the macros to run to specific sheet i.e. (sheet 1) and not any other sheet.

Hope you understand my requirement.

Thanks,

Nitil

excel
MS Office Version: 2016
posted by: NitilA (rep: 14) on Fri Feb 19, 2021 at 6:43 am
Answer
Discuss

Discussion

Nital 

That code relies on Excel "listening for" the Worksheet_Change event (which initiates the running of the code and delivers the variable Target) so won't work on a separate module (as you have found). 

What are you hoping to achieve by transferring it to a separate module (not linked to a sheet)?

Suggest you edit your Question to clarify your aim. 
John_Ru (rep: 6142) Feb 19, '21 at 8:50 am
Please put CODE tags around your code. To do that, edit your post, select your code and click the CODE button.
don (rep: 1989) Feb 19, '21 at 9:52 am
Nital

Please don't say "Hi John" or similar in the Question in future - just use "Revision 1:" (for example) then explain what you've added or what you want to achieve.

I still don't understand what you're trying to do. Your sample code/file doesn't help much (since you don't have the Sheet23 it refers to and I can't imagine why you want to start a timer after each automatic timestamp).

When editting your Question (to address Don's point above) please also explain what your desired link is (between the automatic timestamp and the two timers).
John_Ru (rep: 6142) Feb 19, '21 at 10:50 am
I must admit I did think I was reading an email at first when I saw the Hi John, reminds me of those old message boards that posted email chains online lol. At least he is polite though.

Mr Nitil, you need to use CODE tags my friend - it makes reading the vba code much easier.
don (rep: 1989) Feb 19, '21 at 12:33 pm
"I wanted the macros to give me continuous results in sheet1 inspite of shift from sheet 1 to sheet 2." is about as clear as a London fog. Your macros my_macro and my_macro2 both write to Sheet1 and will continue doing so even if you change the ActiveSheet. But it's not clear whether that is what you want or if it is what you complain about.
Right now we have (1) your workbook with code that probably works but I don't know what you want to improve. (2) the code in your question, which doesn't work and of which I don't understand what you want it to do, and (3) my workbook which tries to fit the code in your question into your workbook.
I shall be glad to delete my answer if you don't want to look at it. However, your paragraph "Further, my requirement ..." doesn't mention one cell or one precise thought. It's just too general to do anything with it.
Variatus (rep: 4889) Feb 20, '21 at 6:48 am
Hi,
I have only two queries:
(1) In "My sample Test excel sheet", After clicking the start button and start2 button, the macro start running and than when I shift to sheet 2, than the macros start showing result in sheet 2 which i don't want. I want the result specific in sheet 1 irrespective i shift to sheet 2.
(2) During the macros running in sheet 1, when I shift to sheet2, the macros stop showing running in sheet 1 which i don't want. I want result continue to run in sheet 1 irrespective i shift to any sheet or workbook.

The both the attachment of "178 TXL 210220 Run 2 Macros" and "Run 2 Macros at Set Intervals v0 c" are fullfilling both my queries, but it is not working in my excel sheet. I am unable to find solution for the same.
Can you please help me for finding the solution.
It would be great help.
Regards,
Nitil
NitilA (rep: 14) Feb 20, '21 at 7:10 am
It would be good to get a clear idea of the business need that this combinbation of macros is meant to address. In the absence of that, we can only guess from the confusing question.
John_Ru (rep: 6142) Feb 20, '21 at 7:14 am
Aah! We're coming closer. We have to fix one of the workbooks. I suggest we fix mine because the code is better and I'm familiar with it (sorry, you aren't familiar with it yet, but it's better, please believe me). So, how we fix it?
From the outset I said I don't know what your two macros should do. Why don't you explain? Like, "Macro1 should write ??? in cells ??? on Sheet???". Then do the same for Macro2. Please observe my choice of words. It's Macro1 and Macro2 that carry out the action, not Timer1 and Timer2. The timers just trigger the macros.
Variatus (rep: 4889) Feb 20, '21 at 7:06 pm
Hi,
Please find attached my latest file i.e. "My sample Test excel sheet" which is attached to Revision 2 edited to my question.
Macro1 should write 1 after every interval
Macro2 should write 2 after every interval
Timestamp to capture the changes in sheet.
Pivot table is linked to sheet 1 where i can see the change in sheet with updated time.
Here, my worry is when the macros is running and whenever i shift to any other sheet, the macros overwrite in other sheet which i have shifted to. I wanted the macros to run to specific sheet rather any sheet which is shifted to view. Please help me to limit the macros to run to specific sheet i.e. (sheet 1) and not any other sheet.

Hope you understand my query and will able to resolve the issue. Thanks,
Nitil
NitilA (rep: 14) Feb 21, '21 at 1:24 am
Add to Discussion

Answers

0
Selected Answer

I have added comments to your original macro procedure.

Sub my_macro2()

    Dim x As Integer

    ' this line only wastes time.
    Sheet1.Range("D2:D10") = Sheet1.Range("D2:D10")

    For x = 2 To 10
        ' this line writes on the ActiveSheet
        ' when you change sheets it will still write on the ActiveSheet
        Cells(x, 4).Value = 2

        ' if you want it to write on a partcular sheet you must add a qualifier
        Sheet1.Cells(x, 4).Value = 2
        ' or perhaps
        Worksheets("Sheet1").Cells(x, 4).Value = 2
    Next
    Call macro_timer2
End Sub

The difference is between "Sheet1" and Worksheets("Sheet1") is that the latter uses the name the user gives to the tab. If the user changes the sheet's name the code won't find it. The former, on the other hand, uses the name Excel gave to the worksheet when it was created. The name can only be changed in the VB Editor.

posted by: Variatus (rep: 4889) on Sun Feb 21, 2021 at 9:42 pm
Discuss

Discussion

Thank you so much Variatus for your solution.
Further, Can you please guide me, If I want to write on a particular sheet of particular worksbook than what will be the qualifier or the coding.
So, that the macros to run in particular sheet in specific workbook even If i shift to another workbook.

Thanks,
Nitil
NitilA (rep: 14) Feb 22, '21 at 5:53 am
Nitil, there is a misconception here. Code doesn't "run in a particular workbook". Code is a tool used by an application, in this case Excel. It has access to all worksheets in all open workbooks and even beyond that, it can open closed workbooks and access them. Therefore you should look at what the code does, not for who calls the code.
In this case, the code is called by the Timer, which isn't even Excel - it belongs to Windows. The code runs in VBA, which is a tool Excel uses, but you focus on what it does. It writes to specific cells. You can specify the worksheet where these cells are located but if you don't specify VBA will presume it's the ActiveSheet. You can specify the workbook in which the targeting sheet is located but if you don't specify a workbook VBA will presume it to be the ActiveWorkbook.
It doesdn't matter where you click or what you do, the code will run when the Timer calls it. At that time it will interrupt what you are doing and do what it is programmed to do. - If it is programmed to write to the ActiveSheet it will write to the sheet you made active at that time. If you want it to write to another sheet you must program it to write to the sheet you want it to write to.
Variatus (rep: 4889) Feb 22, '21 at 8:33 am
Thank you very much, Variatus, John, Don and all the team member of Teachexcel.
Thanks for your guidance and technical support.
NitilA (rep: 14) Feb 23, '21 at 5:00 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login