0
I'm new to creating Macros in Excel and have been trying to understand VBA by taking apart Macros others have made.
What I'm trying to create right now is a Start/Stop timer of sorts to measure how much time is spent on different projects and on different tasks within projects.
I want my user to select the project (from a drop down I've created located in A2) and task type (also from a drop down, located in B2) and then click a button "Start" (all on Sheet 1). This will then run a macro to transfer those two inputs to the next available row on Sheet 2 (columns A & B) and then input the current date (column C) and current time (column D). When they're done working they then click another button "Stop" (also on sheet 1) to insert the current time onto Sheet 2 Column E (same row as data used for "start").
So it needs to:
Click on the Start Button
- Copy the A2 and B2 Values to the next blank row in Sheet 2
- In Column C of that same row input the current date
- In column D of that same row input the current time
Click on the Stop Button
In (the same row as the other data just added) in column E input the current time
Or last D row that has a value input current time in E
I tried using this:
'Start Button
'Determine emptyRow
ActiveWorkbook.Sheets("Sheet2").Select
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = A2.Value
Cells(emptyRow, 2).Value = B2.Value
Cells(emptyRow, 3).Value = Today().Value
Cells(emptyRow, 4).Value = Time().Value
(Different Macro)
'Stop Button
'Make Sheet2 active
Sheet2.Activate
'Determine emptyRow
ActiveWorkbook.Sheets("Sheet").Select
emptyRow = WorksheetFunction.CountA(Range("E:E")) + 1
'Input Stop Time
Cells(emptyRow, 5).Value = Time().Value
And using this:
'Start Button
Sheets(“Sheet1”).Select
Range(“A2”).Copy
Sheets(“Sheet2”).Select
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
ActiveSheet.Paste
They didn't error but they didn't work either so I know I'm missing something.
Formatting would greatly help...i don't understand why you need a timer.what would happen if you didn't click stop when you do in your example? – Dave – 2016-08-29T20:20:10.260
1"They didn't error but they didn't work either" - what they do? Your question is very vague now. Please debug your code and let us know where is behaves wrongly. – Máté Juhász – 2016-08-29T20:22:33.740
This set of code didn't turn red in the edit window and didn't result in the "error/debug" message. When I run it though (click the button it's attached to) it doesn't do what I expect it to do (copy/paste). As far as I can tell it doesn't do anything to any of the sheets. – Mel – 2016-08-31T12:55:37.563
The overall purpose is to log how much time is spent on different projects and within those projects how much time is spent on different tasks. By using the start and stop buttons a running log is created of start/stop times for projects/tasks. This data then feeds into another sheet (that is "traditional" formula based) that totals all the data points into a summary based on project/task. – Mel – 2016-08-31T13:09:18.563
If someone doesn't click the stop button at the end, that data point (row) becomes "useless" (or will have to be manually manipulated later).That's why E (stop time) should probably look to last row with a value in D (start time) as opposed to "next empty cell in E". If I can get the basic set-up to work I could always add something in later to pop-up a message if you try to close the workbook or hit the start button again without first hitting stop (this isn't as important though). – Mel – 2016-08-31T13:09:26.070