Creating a stop watch on my VBA form & Combo Box items dissapear after pressing submit.

[diskin8]

So I have created a basic tracker for users in the office for what they are doing throughout the day, the form submits the data to s spread sheet.

My first & main issue is my combo box, everytime I submit the information to the spreadsheet and then return to the form and the combo box items have dissapeared? Anybody any ideas?

Also you can see from my tool I ask the user to enter the time they entered the given state and how many minutes the spent in it, I'm looking for an alternative to this - my idea is to create a stop watch for when the user enters the state they can start the clock, then when they leave the state they can stop the clock or perhaps even a button to take note of the time value, then a button again that takes note of the 2nd time value, the first time value will act as the state start time & the second will act as the state finish time.

Thank you to anybody that can offert their assistance

[dflak]

I got into this and things sort of got away from me. I did a lot of revision.

First of all there is the tables sheet. This sheet has three things:

A table of agent names. You can maintain this list with any amount of names. No need to add the names to the code.

A table of States. Ditto.

Some helper cells.

The selected agent is in Cell F1.

Cell F2 figures out the maximum date/time for the agent.

Cell F3 is a composite of the agent’s name and the maximum date. This matches the helper column in the Data table.

Cell F4 finds the row for the latest date for the agent

Cell F5 is the current state.

Cell F6 is the time the current stat was initiated.

The button now launches a new form that only gets the agent name. When you click the continue button, the selected agent’s name is written to cell F1 on the Tables sheet and the calculations happen.

The button also launches the Tracker form. My apologies, my naming convention is backwards from yours: I put the type of object first and then the name.

The tracking form gets most of its information from the table sheet. It assigns the current time as the end time and computes the minutes spent.

Enter in the new state, who approved and click the submit data button.

The submit data button looks for the old current state and if it finds it fills in the end time and minutes spent in state.

It also adds the new state as a new record.

The only issue is that the minutes spent won’t work over midnight. I need to know how you want to resolve this. I can do two things. I can include the date and the time so you’ll get the number of minutes between the last thing you did yesterday and the first thing you did today, or I can detect if it is a new day and start fresh each calendar day. Which do you prefer?

Also, instead of minutes spent in state, I could present the time as hh:mm.