Macro needed to increase a cell value and start countdown timer

[Klayton]

Hi I'm new here so bear with me.

I need a macro to increase the value of cell A1 and start a countdown timer in cell A3 that auto resets after the macro finishes. Searching the web has yielded nil. I did find a way to increase the cell count and a way to make a counter BUT, the counter doesn't auto reset and I can't get 1 macro to increase the cell count and count down.

----------------------------------------------

Please Login or Register  to view this content.

any help would be greatly appreciated

-Klayton


Moderator's Note: Welcome to the forum and thank you for joining. Btw when posting codes be sure to enclosed it with code tags..Select the codes the hit the "#" symbol, I'll do it for now. Thank's again.

[AndyLitch]

I'm not entirely clear on what you're trying to achieve here... If you could explain it in two or three sentences it would be really helpful.. What is the purpose of the timer?

[Klayton]

Ive made macros to sort/move/archive data from one place to another but I've never made macros to make a counter, which was surprisingly easy, or a countdown timer, which I had to look up. I have no idea how to code macros to do two different actions. The only way I've made macros before is by recording what I do and letting the computer make the code for it.

The purpose of the timer is to give a visual to our production line so they don't exceed a production speed of 56 seconds per piece produced. The counter is to keep track of the number of pieces they produce.

The macro will work off of a switch located at the top of the clamp that automatically gets triggered each time the hydraulic clamp is raised and will trigger the macro each time the sensor is triggered.

If possible it would be nice to have an easy way to change the timer but from what I found online you can't make a cell to do that you have to make a macro. This was from a thread in 2007 so maybe there has been an update since to change that. If not it's not a problem I can manually change it if they need.

I used cell A1 and A3 because I can change the placement of these as needed to make the screen they way we want. I'm just having trouble getting the counter macro and the timer macro to work as a single macro and getting the timer macro to auto reset the timer each time it reaches 0. Currently when it reaches 0 the timer stays at 0 and if you start the macro again it merely counts to -1 and stops. I spent about 4h yesterday looking up a way to auto reset the timer and found nothing on the internet so I figured I'd ask the experts.

Eventually we would like to add another macro that every time the line cuts off will start a running timer to track the downtime on the line but were starting with production speed control first.


Much appreciated for your fast response

-KK

[AndyLitch]

Sorry for the delay but the server has been playing up a bit.

Try this simulator

Production Speed Sim.xls

I'm assuming that operation of the hydraulic ram equals one production piece. The reset triggers automatically at the start of a new day...(That may not be the reset you want)

[Klayton]

While what you gave me is a good sheet it's not what I need. I need a way to limit our production speed because what is happening is the people making the first piece in our line are making them too quickly. They are suppose to put out 1 piece every 56 seconds, that number changes depending on our demand but we tend to have a takt time of 56 seconds because we have a fairly consistent customer demand, but what is happening is they are making a piece in 1/2 or at times 1/3 of that. We want a visual to limit their speed and show them the current production number because right now they are looking at our quality inspection station to get a guess of the run but if a part is defective it doesn't get scanned out it goes to repair so it's not accurate as to how many total pieces have been run. We believe that this is influencing their production speed which causes a nightmare for our QC's because everything is crammed together and the amount of defects go up. When I get home I'll put a mock up of what I have working so far and how I want it to look.

[Klayton]

ASSEMBLY MACRO.xlsm

We want the counter to increase each time the clamp is raised and we want the countdown clock to start each time the clamp is raised. I've thought of a better way to make the countdown clock work I just can't get the code to work. I think if the macro increased the count, set the count down timer to 56 seconds, and then started the countdown it would be better. I tried using code I found online to increase the countdown clock but couldn't get it to work.

I have a question but I don't know if it's possible. Is it possible to have the macro create a time stamp each time the clamp is raised and if the workers raise the clamp again before the count down timer reaches 0 it records the total elapsed build time. Ideally this would be on a separate sheet.

If you set the countdown timer to 10 and click the button you can see what I have gotten to work. The total down time is for a stopwatch that will start each time someone cuts the line off. That's not important to have now though what we want the macro for that to do is to start each time the line cuts off and create a time stamp and record in the cell beside the time stamp the total amount of time the line was off. I'll create a mock up if anyone wants to tackle this though the first problem listed above is more important.

-Thank you again for your help

Note: I modified the spreadsheet to include what we would like for the time stamps. It's on sheet 2. I should probably clarify something. The amount of down time on sheet is the amount of down time added at that time stamp not the total amount of down time at the time stamp

[AndyLitch]

Ontime Demo.xlsm

Attached file is an Application.Ontime demo which is what I think you need. Using this you can schedule a macro to run at a specific time..

[Klayton]

Not quite sure what that is suppose to be doing other than a stop and go display. While useful we want the workers to be able to see how much build time is left so they can adjust their speed. A simple green light that then switches to red when they should be done will probably do more harm than good because they don't know when it'll change.

[AndyLitch]

Ontime Demo2.xlsm

See this file.
Operation of the jack triggers the macro which then generates an inhibit signal for a fixed period of time before resetting.
The time period is set in B2

[Klayton]

I got that after fiddling with it it's just not what we need.

[AndyLitch]

Fair enough.... good luck

[Klayton]

What I need help with is modifying my countdown code to increase the cell value of b3 to 56 first and then starting the countdown clock.

[Klayton]

ASSEMBLY MACRO.xlsm

K this is the update on my code. I've found a way to set the timer value and use a macro to call each macro. This makes it do what I want but I was wondering if there was a way to change this

Please Login or Register  to view this content.

to have the time value that gets added like this:

Please Login or Register  to view this content.

I had to convert the time to a number format and use that. Then I found out I needed to set it to 11 because it immediately changes to 10 after running and this was the only way to get a true 10s count down.

and is there a way to have it so if i run the macro before it finishes counting down it stops the previous macro then resets the timer to the value I had set. Currently if you do this it adds the value to what's left on the timer and makes the clock run twice as fast.

[Klayton]

Bump

Primary Help

I was wondering if anyone else could help me with getting my macro to cancel the previously running macro and then run the macro again. Currently if you run the macro while the countdown timer is running it will add 10 seconds to what is left and then count down twice as fast. A Google search yield little in the way of canceling a macro that needs to be run again right after the cancel.


The code I use to call all of my macros is:

Please Login or Register  to view this content.

Each Macro has this for code:

Timer Increase Code:

Please Login or Register  to view this content.

Counter Increase Code:

Please Login or Register  to view this content.

Timer Code:

Please Login or Register  to view this content.

Secondary Help (Solved this myself)

I would also like to know if it is possible to incorporate the macro from the Production Speed Sim.xls file that andy posted. The plant manager likes the time stamp to keep track of how fast they are building, if they aren't using the full countdown timer time or are taking longer than the timer, but in trying to copy the macro over I cant get it to work without command buttons. I would like to just call it up with my other macros.

The only difference I want to make is that I want this macro to put the time stamp on sheet two and I need to modify it's placement. If you look at my file, which I just updated again ASSEMBLY MACRO.xlsm, on sheet two in cells A1 and B1 I have titles I would like to stay permanently. The titles are Clamp Time Stamp and Part Build Time. Part build time will be calculated by the difference between the time stamps just like the macro Andy suggested does.

Currently the macro Andy posted starts the time stamps in cell B11 and starts the build time in cell C12. I would like to change this to start the time stamp in cell A2 and the build time in cell B2. I also don't need the amcro to put a number value in the D column like it currently does so this can be left out.



I've posted all of my code below to make the information easier to get

The code I was trying to use was:

Please Login or Register  to view this content.

The code I got to work is:

Please Login or Register  to view this content.

Any help is much appreciated

-Klayton