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

Worksheet event for SheetCalculate

0

Hi Variatus,

In the attached excel sheet, The report is generating based on manually written "True" in the cell. Instead, I am looking for the report based on formula based "True" which is Remote link to another sheet. 

I am looking for result without manual intervention (manual data feed).

I got the same kind of solution in my previous query i.e."Record Number of counts to formula based cell - Excel" but i am failing implement the same in this sheet.

Can you please help me for the solution.

Regards,

Answer
Discuss

Answers

0

Please try the attached workbook. It all starts with the Workbook_Open procedure in the ThisWorkbook module. When the workbook is opened the procedure SetSchedule is called. You can find it in the TXL_5475 module. This procedure sets a time to run the procedure CheckForUpdates every minute. You can change the interval here. Try to make it the same as the interval by which your external program updates your Input sheet.

And it also all ends in the ThisWorkbook module because just before the workbook is closed the Workbook_BeforeClose procedure runs automatically. It calls the procedure EndSchedule (also in module TXL_5475) which terminates the timer. You can also run that procedure on F5 (Run Sub) if you want to stop the automatic running of the updater, and after you stopped it you can restart it by running SetSchedule manually (F5).

You can also run the procedure CheckForUpdates manually (F5). But normally you won't need to because it runs fully automatically every minute while the workbook is open. It checks all the TRUE cells on the Input tab (E5:G9), and if the same cell wasn't TRUE before it modifies columns H:I and makes an entry in the Activity log.

When you first open the workbook the program doesn't know which was the previous cell content in E5:G9. Therefore it changes the update time (not the Comments) and makes a log entry for every TRUE found. This happens every morning and after every crash. I didn't deal with this problem because any solution depends upon your larger work flow. You may, in fact, want these entries. Anyway, note that a solution would require more information about your workbook and your work flow than you have provided. Therefore it would need to be presented in a new thread. But first, I suggest that you try you own hand. Good luck with that!

Discuss

Discussion

Hi Variatus,
Thanks for your response.
The another sheet (i.e.Remote data link) is data feed from external party which is automated feed the excel through their formula base.
I want that data to be feed to input sheet which is formulated (linked) to that another sheet.
The input sheet is the base file for my data analysis from which I am looking for my analysis capture in Activity Report.
Trust you understand the requirement.
Let me know, if you require more understanding of the requirement.
Please help me for the solution.
Thanks
SunilA (rep: 56) Jun 9, '21 at 9:06 am
Add to Discussion


Answer the Question

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