Expanding Excel VBA project

Hello,

First post. Sorry, this may be long. My VBA experience is very minimal.

Project in a nutshell:
I am creating a very basic way for our technicians to timestamp radio transitions, and a very basic transmission quality scoring (1-5). Here is a screenshot and explanation of what I have now,

VSS_UI.jpg

All of the references bellow will be for "Event 1" all the code for the different events is identical, except for cell references and button names.

A) This is a Harvard sentence that is pulled from the "Phrases" tab. This cell also has conditional formatting so that it will highlight green when the "Event start time" = "System Time" as a reminder for the user to transmit.
B) "Start" button. The user will press this button right before they transmit. When pressed this put the current time in the cell before it (C3). This also checks if there is already a time stamp in the cell and if there is has a message box to warn the user.
C) "Stop" button. This is identical to the "Start" button. The user will hit this button after they are finished transmitting.
D) "Clear All" button. This button will clear all the data from the "Start Time", "End Time" and "Score" columns. There is also a message box that will warn the user.
E) This is a dropdown that pulls a list of name from the "Users" tab
F) "Start System Time" button. This will start a function in excel to update cell (H1) with the current time every second.
G) This is the individual events start time. In event 1, event start time = start time (H). Event 2 start time = Event 1 start time + Interval (I) etc.
H) This is the initial start time for event 1. This is entered by the user.
I) This is the time between events. This is entered by the user.
J) "1-5" buttons. This is a basic scoring system. When the receiving operator hears the transmission they will score the quality. When one of the buttons (1-5) is pressed the score will be written to cell (D4)

Now onto the code. This will be from "Event 1" all the code for the other events is identical except for names and references. All of this code is in the "Sheet 1 "Data Entry"" file, with the exception of the system clock function (noted below).

"Start" button

Please Login or Register  to view this content.

"Stop" button

Please Login or Register  to view this content.

"1-5" buttons

Please Login or Register  to view this content.

"Clear All" button

Please Login or Register  to view this content.

"Start System Time" button. Part 1, located in the Sheet 1 file.

Please Login or Register  to view this content.

"Start System Time" button. Part 2, located in the module file.

Please Login or Register  to view this content.

I have a couple problems/questions.

1) Am I going about this the wrong way? Is there a better way than creating individual buttons for all the different events? I had thought about doing a dropdown instead of the "1-5" buttons. That would cut down on the code.

2) I was experimenting with a toggle button for the system time (off to the right in the screen shot) and I could not get it to stop the time once it was running. Am I going about getting the system time the wrong way? This is a very small issue, I do not see any major need to stop and start the system time.

2) This is my initial trial build to see what the technicians like/don't like, they were using paper to write this stuff down. The final version will need to have ~ 100 events. This leads me to my main question. How can I expand this in the least painful way? My current method would be to copy/paste what I have and then go to every button change the name, copy/paste the code, change the cell references in the code etc. Is there a better way?

I don't seem to be able to attach the file, which may make this a little easier to understand.

Thank you for any help.

Firstly, don't use ActiveX controls on worksheets, they're incredibly unstable and asking for trouble - use forms controls or shapes and assign them a Macro.

You can loop through shapes/forms controls on a worksheet and assign them macros, you'd have one for each action. So a single Macro for Start - you'd then use the Application.Caller property to see which button called the macro and use its location to work out which cell it is in. Once you know where it is, use relative referencing (offset) to change the cells (rather than Absolute references).

Finally, why not just let people type the score in rather than clicking a button?

Thank you! I will remove the ActiveX buttons and replace them with Form buttons. That sounds like it will make it much easier to expand.