+ Reply to Thread
Results 1 to 85 of 85

Multiple timer with looping audio until acknowledged

  1. #1
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Multiple timer with looping audio until acknowledged

    I would like to start with my coding ability is very limited. With that said I have scoured the internet for the last couple days mulling over previous VBA codes looking for something that will meet my needs. But unfortunately my search has resulted in nothing that I seen would give the results i am looking for. I am needing a count down timer that will initiate when the time is keyed into a certain cell. The timer will always countdown from 24 hours. Hours and minutes are the only two parts of data I need if that makes it easier. Also I would like to have an audible alarm loop once the timer is null until either a box is clicked or check box is clicked to acknowledge the timer has ended. To give an idea of the data this is for samples taken and must be checked 24 hrs after time that is put into the spreadsheet. This same spreadsheet will be used for revolving data through an entire year so the total entries could exceed 1,000 but should not exceed 2,000. I have read in my search how many entries may result in an inaccurate timer. Is this something that is possible to accomplish? Im not asking for someone to do the coding for me but hoping someone may be able to lead me in a direction to where I could compile a couple code variations together to get the result i'm looking for. This may be extremely simple to some but for me i am totally lost. Any help would be greatly appreciated. Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Hi, welcome to the forum.
    Programming timers and especially more than one requires some skill.
    What do you mean with Countdown 24 hours fro the time entered en Column C? If you enter 14:11:23 what is the 24 hours? Is that 14:11:23 of the next day?
    If you use multiple timers these won't really work fine, at least that is my experience.
    I'll look up some code I have used previously for another OP on this forum.
    You could search for it in the forum, I don't recall the name but it's there
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Thank you for your reply. Yes so for example if i keyed in 7/23/19 15:50 i need the timer to alarm at 7/24/19 15:50

  4. #4
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    I was doing some thinking and thought it may make it easier if i could narrow down the amount of timers... what if i added a checkbox and only the ones that were checked were sent to another sheet and that sheet contained the timer.. The most that would be active at once would be 20. Don't know if that would help but was thinking about it..

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    The thing is that you do not need a separate timer for every countdown, I don't know if I can explain myself correctly but I'll try.
    The timer will run the moment you open the file and you have also to include the code to end the timer when the file closes.
    My question is, are you going to keep the file opened all day? If not then I don't know how to solve it for you.
    You mention 20 'clocks', I helped a guy with 10 clocks, lookup the username vinospam, the post's subject was something like Countdown timer
    Here he wanted to pause, continue or restart one or more of the 10 timers at any moment

  6. #6
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Yes the file in question stays open all the time. However periodically the pc needs to restart and/or the pc will sleep due to inactivity. I think I have read in some of my research that a certain language of code will allow the timer to continue working if the file closes as long as another file is not opened. The timer you suggested to look at I think is the one I came across late last night and I anticipate working with it to solve my issue. However I am still in the dark on the loop sound. I can find code for a single beep but I need like a siren that will loop until it is acknowledge or manually disabled. I greatly appreciate your assistance.

  7. #7
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Keebellah let me ask you this. Would it be easier to not worry with the timer and use a different path for my end result which is an audible alarm to alert the sample is ready to be checked? I will try to explain in detail... I am not worried at all about seeing the timer countdown 24hrs. all i am wanting is for example the first set of samples on my worksheet, is an alarm to sound at 14:36 on 7/24/19 which is 24 hrs from the time i entered the information into the pc. I dont know if the spreadsheet could compare my entered time to real time and only sound a looping alarm at the precise time of 14:36 on 7/24/19.. I just thought the timer would be easier but maybe you or someone else has an easier alternative to reach my end goal. Also i am currently working on my spreadsheet using some code for the timers and will post what I have so far this evening. Thanks.

  8. #8
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Yeah that could be done too, but still you need a timer or action somewhere that checks if the target time has been reached.
    So still there is a timer running that will trigger the 'alarm sound' at the right moment.
    I'll think about it too, right now I'm not enrtirely fit for computing due to a cataract operation on my right

  9. #9
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    This is the one I also helped with, the sound files was the OP's but making some modifications (I'm doing them now) will probably make it work.
    I've attached the file I modified for the OP so that you can play around with it.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    OK thank you. I will play around with it and add to what i have going so far. Sorry for the delay in response but was real busy today..

  11. #11
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Hi there, just curious, how's it going, did you manage to get something together with the sample file and code?

  12. #12
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Hello, yes this past weekend has been very busy but i have managed to piece something together. in the attached file you will find an updated version with the code you supplied for the timers but it isn't working right. I am by no means a coder so i have not the slightest clue what is going on. What i would like is for the alarm to trigger on the timer sheet once the checkbox is clicked. and countdown 24 hours from the time in the table. would you have any suggestions on a solution? thanks
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Was thinking also if it would make coding easier, the timer will never be variable so if it could be setup to always countdown from 24:00:00 would be great. Instead of referencing the start of the timer with the time keyed into the spreadsheet, could the timer be triggered once the checkbox is selected and just countdown 24 hours from that time?

  14. #14
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    That was my first idea too, it doesn't matter what time you enter it just has to count down 24 hours, but in case you do close the Excel file it should keep track of where it was at that moment?

  15. #15
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Yes. If does close it is very important it keeps track of where it was and picks back up when reopened..

  16. #16
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    I see what you've done with the code so far but there are things that are not going to work properly.
    First you need to declare SchedRecalcas public or Global so that it's recognized across all the macro's
    The named range "Time", which cell? is that supposed to be D4 ?
    Will you have more than the 27 pre-set rows or will this be the maximum number of timers that may run at one time?
    What do you want to do once the time is elapsed and acknowledged/ Delete that timer?
    Do you still want the 'alarm' to sound once the time has elapsed?

  17. #17
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Again I know very little about coding so I am not sure about the “time” range yet. And yes the max would actually be 20 timers but it would probably never have more than 10 at any given time. Once the timer expires and the looping alarm sounds it would require someone to acknowledge it to turn off. Once turned off that timer will disappear because the person would key in the time they checked the sample on sheet2 thus resulting on the status to change to already checked and removing that date and time from the timer sheet. So it would wait for another sample to be keyed in and once the checkbox is checked it would start counting down again.

  18. #18
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Clear, the range names 'Time' is not advisable since Time is also a reserved work (value)

  19. #19
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Could I identify ‘time’ as 24:00:00 rather than as a range?

  20. #20
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    TIME is a reserved word, it might accept is but you might run into issues when using the function TIME()
    You can declare a Global Constant as date with the value 24:00:00

  21. #21
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    After much frustration with searching for codes and making attempts I have decided I will just make the timer trigger manually. I do appreciate the help you have given and the code you provided. I will use your multi timer code provided in my ws. Again thanks for your help.

  22. #22
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    I did start on it, but due to lack of time ....
    The attached file starts the timer automatically and checks
    Row 3 has been filled
    Just enter a date and time in row 4 and the rest starts.
    No timer pause or things like that but that's the following step.
    Just to help you on, no sounds and so yet and maybe tomorrow a lot of questions
    Don't despair, Have faith (especially in yourself) and code, it's all in the imagination and inventivity, all VBA does is tell it what YOU want it to do
    Attached Files Attached Files

  23. #23
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    If you still have a little faith left can you explain the process itself.
    When does the user trigger a new sample / test?
    Is pausing an option? Set timer on hold and then continue the countdown to 0 from that point?
    I'm writing questions as the come up. Is it always 24 hour countdown?

  24. #24
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    I've kept playing around with it.
    I got rid of the checkbox, it's a nuisance, replaced it with a right=click action to check / un-check the cell
    The two columns to the right of this 'checkbox' may be hidden.
    What this timer does now, is automatically start when the file is opened and picks up where it left off
    There is no sound yet but the moment the timer has run 24 hours the user must check the box and then the row will also be removed
    If the start time entered is in the future the timer will wait for count down until that moment arrives or has elapsed the next time the file is opened.
    Take a look and see if you grasp my idea, it's late now, here's the file to play with.
    Attached Files Attached Files

  25. #25
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Your favorite stalker is still here.
    No sound yet but blinks red when running
    Attached Files Attached Files

  26. #26
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    This is the last one
    Conditional format that colored the cell stopped the macro from running automatically when the workbook is opened, changed that and now it does the thing.
    It also sorts the table if the user enters a new date and time in a not adjacent row inside the table
    Like to hear from you if this did the trick (expect for the sound )
    Attached Files Attached Files

  27. #27
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Pitty you're no longer interested, got the sound working but.
    Well, was fun

  28. #28
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Keebellah i am still here.. was away on vacation with no service.. i will answer your questions as they were posted... I was just suggesting clicking the check box as a trigger to send the start and stop data and time to the "Timer Sheet" because the master sheet may have over 1000 entries by years end so having a sheet that listed only the active samples I thought would be much easier.. So by selecting the check box would move the info to the timer sheet thus also starting the countdown timer. There will never be a pause in the timer. Once it starts it needs to run continuously for the full 24 hrs. and once the timer expires it needs to alert the timer has expired and continue alerting until acknowledged. A single beep will not suffice due to the fact the computer being used for this data entry is strictly only for this data and is not monitored regularly nor connected to the internet. I will check out what you have done so far and again sorry for the delayed response but should be here more regularly now. Thanks..

  29. #29
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    I like what you have came up with.. I will work with that and see if i can tweak it to meet what im looking for.. what i am looking for is something with 2 sheets.. Sheet 1 will be a master sheet with over 1000 entries by years end.. it will keep all information about the samples taken throughout the calendar year. Sheet 2 would be the timer sheet which would only show the active timers running at that point. Even though sheet 1 may have 50 entries at that point in the year, only 3 may be active. Having a check box or way to show only the current active timers on the timer sheet is a must. once the trigger is selected and the active timer is shown on the timer sheet i would like for the sheet to say "ok here is a new timer, I will count down from 24 hours and then sound an alarm" I really dont need the program to automatically select the end time for me because, even though i submitted the data on (example) 8/12/19 @ 14:00 I may not actually get to read the sample until 8/13/19 @ 14:03. So i need to physically input the 14:03 as the end time on the master sheet. I will work with what you have done so far and see if I can come up with something. I have gotten as far with the sound as making a function soundme() and using an audible alarm. With that i have gotten to when a cell has certain criteria in it i can use the IF( function and sound the alarm. however the alarm does not loop and is only a few seconds long. Thanks

  30. #30
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Hi, good to hear you're still there
    I understand you story and will keep that in mind, but for now, and with what I knew (or thought was your idea) I came up with the attached workaround.
    Only one sheet now for the timer and the sound which will call everybody's attention if the volume is set high
    The file contains a userform that is activated with a right-click in column A inside the table area, if the cell in column A is blank the only option is add new timer and this will be today's data and current time, automatically the end timer is set to 24 hours ahead and the countdown runs as long as the timer is active.
    If you richt-click the column where the checkmark is expected you get the user form with Check/Uncheck
    If you right click in column A and there is a value the option Check/Uncheck is also present as well as reset timer which will set it the current date and time and start the timer for the next 24 hours.
    You also have the option to delete that timer.
    If the 24 hours have elapsed, the check/uncheck cell will turn red and and the buzzer will sound as long as you have not checked the cell (user form) or placing a lower case a in that cell.
    Once the checkmark is placed then that timer will be removed and the table resorted with the remaining timers.
    Give this a run if you want to accelerate it chance the time entry manually in column B by typing a new time value hh:mm:ss
    The worksheet is protected (no password) but the macros are permitted to modify it.
    If you close the file and later reopen it, the time continues based upon what should be the elapsed time, so if the 24 hours elapsed with the file closed, the moment you open it the cell be red and the buzzer will sound.
    Sounds like a lot but give it a test, the timer automatically starts when the file opens. The button on the top left (Cell A1) toggles STOP / START (actually two buttons, the start is hidden when stop is visible and v.v.)
    Much fine tuning and maybe modifications necessary but it's for starters and you get a buzzer that really is noticed if the sound is on
    Attached Files Attached Files

  31. #31
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    some minor modifications
    Used data validation to restrict data entry in columns A, B and E

    Off to bed now here
    Attached Files Attached Files

  32. #32
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Your timer coding is spot on thanks for the awesome help.. I have made a few alterations to the workbook. The way i have it now you should not key any data on the master sheet. All data should be entered in the data entry sheet. This is exactly what i have been trying to get. Another question i have is can the alarm sound be changed to more of a siren type or does it have to remain as the "Ding" sound? Again thanks for you help.. this is flawless. I have scoured the web for info on this exact issue with no clear answer.
    Attached Files Attached Files

  33. #33
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    I embedded a siren but it's short bursts I just used that code am not that knowledgeable about how to play it full before it's called again.
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    I have made an attempt with the alarm loop. you will find my results and explanation in the file on the Master tab. Thanks so much for your help.
    Attached Files Attached Files

  35. #35
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    I was playing around with the Siren.wav and found a short one which is better.
    I'm attaching the previous version with the 'new' siren sound and will look at your file you just attached,
    Let me read your explanation and question and I'll see if I can get something done tomorrow, it's almost midnight here so I'm off to a relatively 'early' night
    I've attached the wav file in zip format but it's already embedded in the Excel file
    Attached Files Attached Files

  36. #36
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    One thing, it is it really necessary that you have two sheets?
    You can use the Timer sheet as default, and enter the date and if no time is entered the timer won't run until you update the start time value.
    It will limit all to one sheet, and the checkboxes are the ones that add extra work.
    Just a checked-unchecked cell value to either stop/pause or resume?
    What is the exact goal you want to achieve? A date and time start and 24 hours after that date and time alarm and until a user acknowledges the siren and then that row of data is cleared or can be reset for another 24 hours?

  37. #37
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Your explanation:
    To give an Idea of what I’m looking for I have made a few alterations, but did not change any coding. So, in the data entry sheet I have it setup in such a way that as the data is entered the user will select the checkbox to activate the timer. Once it is checked the needed information will move to this sheet thus triggering the timer to start. Once the alarm sounds off the user will navigate to the data entry page and uncheck the active box thus ending the timer and turning off the alarm. Again, this is just an idea of what I’m looking for. Your coding is spot on and exactly what I’m looking for. Curious if the sound could be changed to something besides the "Ding" sound to more like a siren?
    Your idea as I understand it:
    Your idea is that users enter data in the Data Entry Sheet and then have the choice to check a checkbox so that that record is copied to the timer sheet and the timers starts,
    After the 24 hours have passed a siren / alarm sounds and the user then unchecks the checked checkbox so that the alarm for that particular record is silenced and also the record in the timer sheet is removed (correct me if I am wrong)
    Caveat: when a record is copied the timer sheet will be sorted (or not) but there is no way whatsoever to know which is the corresponding record in the data sheet, so when the alarm sounds and the user unchecks that checkbox in the data sheet, the code does NOT know which record it is that needs to be removed, what it there are two records triggered at the same time? One is checked first but which one?
    My idea worked-out a little further based upon your explanation:
    I do not know if you need to keep track of the records that have been checked AFTER the complete, if not, then one Master Timer Sheet could be sufficient.
    The user enters the date for each new record the table is automatically sorted at every entry, after the time has been entered the user has the option to uncheck the check / uncheck column and if the 24 hours have not yet elapsed the countdown continues and when the count down is down to 0 the alar sounds, that cell will turn red and the user checks the column as completed and the record is immediately thereafter removed form the list and all move up one.
    The table can grow unlimited (max is over 1 million rows of data! )
    Last edited by Keebellah; 08-14-2019 at 08:58 AM. Reason: Code

  38. #38
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    Yes it is imperative to use the setup with two sheets. As for the check boxes, that was just the way i knew to set it up for what i was looking for. The data you see in my example is small compared to what it will actually look like. The final worksheet will utilize 18 columns. For my example i just showed the data that is relevant to the timer. From my research i think the only way to get by the sound resetting every second is to use the sound function because the function is not associated with the timer reset macro. The soundme code is something i found and then added the snd_loop &H8 to get it to loop. So if there is a way we can turn the sound off in the timer macro so it wont override the soundme function and then trigger the soundme with an if function i think it will be exactly what i need.

  39. #39
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    Sorry for some reason i did not see your entire message but the main data sheet needs to remain in order as the data is put in. I cannot filter it. Ideally the sheet will remain on the last row of data entered and when the alarm is triggered the user will acknowledge it by the time it is due to end. There will never be 2 at the exact time. Maybe more than one but several minutes will be between the two. The worksheet will remain open on the data entry sheet and will probably never navigate to the mastersheet. I was only using the master sheet to trigger the alarms that are active.

  40. #40
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    I made the attempt to use an if(and( saying if time <> "" and timer="",soundme(),stopwav()). Being that once the checkbox is clicked the information populates in the mastersheet and both cells <>blank so the alarm would not sound but as soon as the timer reached 00:00:00 the timer cell would be blank thus sounding the alarm. I could not get it to work with the table data but i could get it to work with normal cells to the side. I guess its because im trying to read data in the table and its not recognized as the same. Hope that makes sense. Also once the timer ran out your alarm would override mine and i know i will sound dumb but i do not know how to turn off the sound in your coding.

  41. #41
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    To turn off the sound in my code just remove the line that says testMySound in the macro check_Timers()
    It occurs twice but only one is actually effective, that's all

    Will read the rest and see what I can do, I still don't think you grasp that fact that the checkbox or any other way to trigger the timer in the Data sheet is linked to the Master sheet so,that's still going to be the thing to solve.
    You say 8 columns in the data sheet,, can you at least attach a file or worksheet with what that looks like, non-sensitive data but just to understand it more.
    Guessing is a bad option to write and develop codes, even though I usually do that and 'think' ' assume' what the user wants, and that's way off

  42. #42
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Attached is a copy of my original worksheet with dummy data. MasterSheet is what i have now and MasterSheet (2) is what my idea would be for it to look like. I like the idea of you check/uncheck cell rather than the check box. This just gives an idea of what i think it should look like. Also I do not want the data entry sheet to be a table for certain reasons. Thanks
    Attached Files Attached Files

  43. #43
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Okay, thanks let me work on this, don't promise any miracles, but maybe I can get something together for you

  44. #44
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Another question, do you object if everything is triggered from only one worksheet? The timer function and everything integrated in the Mater Sheet
    The only thing is what do you want to do once the Analyzed Out Time has been completed? Delete that record?
    What about history, know when what was processed and the the time out was completed earlier? My guess is also that you would want the user to check as completed and that could be before the actual analysis out time?

  45. #45
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    No I can not have any entries on the data entry sheet deleted. I must maintain a complete record of all samples taken for that year and back them up and hold records for five years. I am not opposed to having it all on one sheet.. only reason I used the “timer sheet” is because that was the best way I knew to do it. Honestly all I need is the alarm to activate for the correct sample after that I don’t need to know if it was completed or not. Being that the time out is entered ensures that the sample was checked and recorded. I greatly appreciate your assistance and apologize for my lacking skills. I was expecting to get different codes from members here and compile them but you have went above and beyond anything I expected and greatly appreciate it.

  46. #46
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    After looking thought the sample file you attached I understood that.
    Now I have a link for the timer and the sample record, just for my idea, the sample ID is always unique, correct? Or is there a combination of two fields that make it unique together?
    This so that the record in the timer sheet can be linked for searching.

  47. #47
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    No the id is not unique. Many of them will use the same id several times through the year

  48. #48
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    So, what would be unique key together?
    Lab I’d + customer ?

  49. #49
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    I wish i could share the original but too much sensitive information.. i will create an example. Bd1, Bd2, bd3. Each of those are associated with a certain location and i pull samples from those same sites once per month. I could create another column that could have a nonrepetitive number if that would help. I tried to make a random number generator but everytime you close and reopen the workbook all the numbers would change so it wouldnt work.

  50. #50
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Don’t worry for now, I’m working on a example and will post it when I feel it looks feasible
    As a former prospection geologist I know about sampling and correct registration that why I kept hammering on this but for now it’s okay

  51. #51
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Okay, let's see if I got it all straight.
    I suggest you first read the pdf where I summed up what I understood from your sample file and my interpretation of how you work with this file and the process behind it.
    The attached Excel file is a working sample so see how it goes.
    I'm sure questions will arise but I think I got the basics working.

    Have a fine weekend

  52. #52
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Minor correction, did some testing and found an error, corrected that one, sure there'll be more
    Attached Files Attached Files

  53. #53
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    Ok as soon as i get to a computer to check it out i will. I was able to read your pdf and i believe your assumtion was spot on about the workflow.

  54. #54
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Make sure you test the V1.6.2 but I think I found some more glitches while testing here.
    Let me know what you think of it and in the meantime I'll see if I can find all the issues.
    Troubleshooting is more time consuming than programming
    Hope Murphy stays away too

  55. #55
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    That is way more than i ever expected. awesome job. I did have two bugs appear. first was Sheet1 (Code); Worksheet Change; Fifth line up including EndSub- if target. Offset (0,-5). Value="" then was highlighted. The second was in Timer Module2 (Code); General SortTimerTable; tbl.Sort.Sortfields.AddKey:=Range C "TimerTable [ [ #All ] -that entire line of code was highlighted. Bravo job...

  56. #56
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Was this in v1.6.2?

  57. #57
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    Quote Originally Posted by Keebellah View Post
    Was this in v1.6.2?
    Yes it was that version

  58. #58
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Strange , okay, will look into it, am currently testing another approach which makes it less “vulnerable” but the same functionality

  59. #59
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Well, in the meantime i worked on a entire makeover.
    The attached file, version 1.6.3 is different in the way that all is handled in the Master Sheet.
    I've not had the time to write an instruction manual but that will follow this coming week.
    The idea is now that you just fill in the master sheet, the worksheet is protected (no password) and only the non-formula cells can be edited.
    There is a series of 4 or 5 hidden columns in between the last two visible columns for the trigger purposes.
    To add a new row to the table when the table rows are filled just right click column B inside the table area and a new row will be added below, at the same time the date and time value will be entered but that can be edited.
    The series of required fields are colored using conditional format
    I think that fine tuning might still be necessary here and there but this will make it simpler.
    The alarm will sound if the elapsed monitoring time is completed and only if the Time Out value is not filled in
    I was busy with a userform as menu to make the macros available to the users but did not get to it, the userform is there but no code.
    A splash screen seemed like fun so I added it
    Have fun. I'm off to bed now
    Attached Files Attached Files

  60. #60
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    I will check it out as soon as possible. Thanks

  61. #61
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    No hurry, am currently reinstating the checkbox and with the notification Active or Already Checked, or maybe one or two more depending on the record status.
    The removing or not using the formulas has made it less prone to error
    Good-night from here

  62. #62
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    The updated version is awesome. but i do find maybe one issue. when right clicking in B to add row, if the sheet is unprotected which i would like it to be, it puts the sheet back into protected mode. Some of the users of this sheet are not the best with computers. other than that it is spot on.. timer works great, sound is awesome... well done.. and a huge thanks for your hard work..

  63. #63
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    When the worksheet is protected only the cells that the user have to complete are unlocked, so even if they are less computer-minded it still is the only cells they may use.
    But, I'm resolving the checkbox issues, there is a tutorial/sample about worksheets and checkboxes on the Contextures website, this site is one of the many I'm subscribed to with some great ideas.
    Advisable if you want to go deeper into VBA

  64. #64
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    I will check it out for sure. I want to learn about coding. I can do small things by finding others code and then tweaking it for myself. I have been trying to understand how you got the multitimer to work but have yet to figure it out. With the coding for the sound and the yellow box i dont know where to look for just the code on the multitimer. Before i posted here i made an attempt from some code i found online but it wasnt multiple and also had to click a button to start it. Your help is greatly appreciated.

  65. #65
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    This is the first time I am playing with the sound clip files, you see, I look up code and re-use it and make modifications to my needs and just test.
    Takes time, but that's the way VBA works, it's quite logic and it's just like reading a book, line by line but you need to respect the syntax

  66. #66
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Hi, It's late but this is the next step.
    If you place the cursor in the top left cell you'll see the comments box explaining the symbols in column A and what they do when you right-click one.
    Explanation(s) will follow but now it's really late

    I think I forgot to hide the columns that say hide on the right side of the table
    Attached Files Attached Files

  67. #67
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    The 'Under Development' buttons work now
    Take it for a spin
    Attached Files Attached Files

  68. #68
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    One more
    I wrote a small 2 page document attached as PDF and the file again, since I discovered some minor things while writing and testing
    Attached Files Attached Files

  69. #69
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    thank you sir for all your help.

  70. #70
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Was challenging and nice to do.
    You're welcome

  71. #71
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    I have another question. So I am trying to understand how the code works and from my perspective its all Chinese. Would it be possible to break it down and make it easier for me to understand. Like if i wanted to just use just the sound on a different project what would i put into VBA to accomplish that or if i just wanted to use the multiple timers on a different project what part or parts of the code would i use just to get the timers on a sheet. Also your sheet works very well. Thanks

  72. #72
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    The sound is just that macro, it will play the sound if the file is present.
    It doesn't need the timer for that
    All the code is in separate modules, just read it and you'll see where each is invoked

  73. #73
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    ok so if i wanted to have just multiple timers on another project i just copy the code in the Timer module? I may have something else that would benefit from just the timers.

  74. #74
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    There is no real timer project.
    It's just a macro that is triggered every second, the triggered macro does what you want it to do.
    Just design your workbook as you want, and place all your normal macro's in it. and test if works just as it should.
    The timer part is just an extra macro that restarts itself every 1 second or the interval you want it to restart.
    Inside that macro you invoke the macro YOU have that does th calculations or whatever you want it to do and after your macro is completed the next trigger will be started.

    Below is the code from the file I helped you with:

    Please Login or Register  to view this content.
    The line in read will then be the macro you want to run
    After it completes, SetTime is called again that sets the next trigger.

    You can just copy all the code but then you will have to make sure the macro code corresponds to the file you want to implement it in.

    I know this all sounds like Chinese, but if you read all the code and especially the SetTime and Recalc line by line and try to think of what it is doing then you will start to understand how it really works, it's not magic, it's just logic and 1 + 1 is always 2 that's all it really is, a series of instructions carried out one after the other following strict syntax rules,

  75. #75
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38
    Yes this is very much like Chinese to me. Please excuse my stupidity but i have made several attempts to figure out the timer but still can not get it. I think it may be because the code provided in this workbook depends on other several named processes before completeing the task. I have tried to erase things i thought i wouldnt need but can not get it to work. Would you be ever so kind to take a blank worksheet and put the code for just the timer. I would only need three rows and no alarm. Just the timer. I would greatly appreciate your help.

  76. #76
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    Attach a workbook with what you want, I'll place the code, no problem

  77. #77
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    This is what i am looking to add the timer to. You will see it has a run timer column which is how long i would like the timer to run. I greatly appreciate your help.
    Attached Files Attached Files

  78. #78
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    I've downloaded the file, will look at it this coming weekend

  79. #79
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    I have not forgotten you, it's just that I could not find the time to sit down and implement the solution for you.
    Don't worry, it's coming
    , just a little (more) patience

  80. #80
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    No worries. Thanks

  81. #81
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    This is what I could do for you.
    I took out all the unnecessary code, and just left the minimal needed timer part.
    All it does is turn red when the time has been completed
    I set the values to the future so it won't run until tomorrow morning early
    Attached Files Attached Files

  82. #82
    Registered User
    Join Date
    07-23-2019
    Location
    TN
    MS-Off Ver
    2016
    Posts
    38

    Re: Multiple timer with looping audio until acknowledged

    Once again an awesome job. Thank you very much. I'm starting to understand the coding a little better now.

  83. #83
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Multiple timer with looping audio until acknowledged

    It’s simpler without the sound part
    Glad to have helped

  84. #84
    Registered User
    Join Date
    11-20-2021
    Location
    Philippines
    MS-Off Ver
    2019
    Posts
    1

    Re: Multiple timer with looping audio until acknowledged

    Hello, can anyone help to timer code into specific excel cell, then once timer is over, message will appear "Your time is up", thank you

  85. #85
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Multiple timer with looping audio until acknowledged

    Quote Originally Posted by bhie0782 View Post
    Hello, can anyone help to timer code into specific excel cell, then once timer is over, message will appear "Your time is up", thank you
    Administrative Note:

    Welcome to the forum.

    We are happy to help, however whilst you feel your request is similar to this thread, experience has shown that things soon get confusing when answers refer to particular cells/ranges/sheets which are unique to your post and not relevant to the original.

    Please see Forum Rule #4 about hijacking and start a new thread for your query.

    If you are not familiar with how to start a new thread see the FAQ: How to start a new thread
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] hwnd timer pause VBA countdown timer
    By jaryszek in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-05-2019, 04:25 AM
  2. Countdown timer Working BUT starting the timer repeatedly SPEEDS UP TIMER!
    By Vinospam in forum Excel Programming / VBA / Macros
    Replies: 67
    Last Post: 03-30-2019, 06:42 PM
  3. [SOLVED] Multiple Timer
    By Luke78 in forum Excel Programming / VBA / Macros
    Replies: 44
    Last Post: 11-22-2018, 09:58 AM
  4. timer for 30 minutes and timer based events
    By mohak12 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-11-2014, 06:20 PM
  5. Replies: 1
    Last Post: 12-12-2012, 08:46 PM
  6. Excel 2007 : # of processors acknowledged by Excel
    By jmckenzie in forum Excel General
    Replies: 5
    Last Post: 06-02-2009, 01:40 PM
  7. [SOLVED] Stopping a Timer / Running a timer simultaneously on Excel
    By Paul23 in forum Excel General
    Replies: 1
    Last Post: 03-10-2006, 08:10 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1