LinkBack URL
About LinkBacks
Elapsed Timer with Update button with a color-coding scheme wrt the values in the cells
Elapsed Timer with Update button with a color-coding scheme wrt the values in the cells
Last edited by snkhan; 06-24-2020 at 04:00 AM.
Excel will automatically adjust the formula for each row...
Please Login or Register to view this content.
Alternatively...
Please Login or Register to view this content.
Last edited by AlphaFrog; 06-17-2020 at 08:26 PM.
Surround your VBA code with CODE tags e.g.;
[CODE]your VBA code here[/CODE]
The # button in the forum editor will apply CODE tags around your selected text.
Hello AlphaFrog,
thank you for the reply. It works, well partially. I have created a button on the first worksheet and when this button is pressed the time elapsed (the code you provided) should run on the second sheet but it only runs on the first sheet instead. this is the code:
for the button
and this is in the module:Please Login or Register to view this content.
please point out what am I doing wrong, thank u in advancePlease Login or Register to view this content.
sorry forgot to attach the file
Please Login or Register to view this content.
Avoid using Select, Selection and Activate in your code. Use With ... End With instead.
You can show your appreciation for those that have helped you by clicking the * at the bottom left of any of their posts.
thank you, it works but I don't want to show the formula in the cells, just the resultant value. how can I achieve this last part? thank u
Please Login or Register to view this content.
An alternative method is to use Power Query which is available in Excel 2010 as an Add - In. Here is the Mcode that is developed when using the U/I.
Review PQPlease Login or Register to view this content.
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
Excel 2016 (Windows) 32 bit
Carrier Time in Total Hours
Sheet: Sheet2
Last edited by alansidman; 06-22-2020 at 02:14 AM.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
thank you, works perfectly
it works perfectly but now I have another feature that I would like to add. I want to add a status column. I don't want the timer to update if the status column for that corresponding time/date is "Complete". please see attached file.
or should I start a new thread for this feature?
thank u in advance
Use this to update like before...
Please Login or Register to view this content.
Use this to "lock" the time when you change the status to complete. It wont lock in time for rows that were changed to "Complete" previous to using this code.
To install the code:
- Right-click on the sheet tab.
- Select View Code from the pop-up context menu.
- Paste the code from below in the worksheet's code module.
Please Login or Register to view this content.
hello AlphaFrog,
I have tried what you suggested and it is not working. After the complete status has been initialized and then the update is run, the 'completed' elapsed time still changes. please see attached file.
Please Login or Register to view this content.
Your Update procedure is not the same as the one I suggested in post #11Originally Posted by snkhan
The data in your example workbooks keeps changing. In post #4, column B had formulas. In post 12, column B is all constants; no formulas. Which do you want; formulas or constant in column B ?
I don't want the formulas to show, just the resultant constants please and thnx
Use Bakerman2's code in post 13. You can delete the Worksheet_Change event procedure.
Thank you very much AlphaFrog and Bakerman2. I was able to get the results I wanted. I also incorporated a color scheme using the same code Bakerman2 provided. (wasn't able to get the value as a constant but as formula, but that's ok I guess for now), Im pleased. Thank you again. I have attached the final product.
You're welcome and thanks for rep+.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks