How to get Command Button on UserForm to change color while code processes.

I have a workbook in which we track daily inspections of our facility. To make the "findings" easier to input, I have a UserForm titled "RecordForm" that opens when requested where all the findings can be input. Once everything is input into the form, you save the record by clicking an add button (the cmdAdd button in the code) and it begins copying all the data to a separate worksheet within the workbook. Once everything is copied, it clears all the entry fields and is ready for the next entry. My problem is that because of the combinations of inspectors, areas, and possible issues they might find, it takes a fair amount of time to copy everything from the userform to the worksheet, but since there is no significant change to the Userform or the Add Button, the user may not realize that it is already working and keep pressing it.

I'd like to have the "Add Record" button change from the standard gray to red while everything copies and once its done, go back to gray. Unfortunately, everything is copying over to the "Historical Data" worksheet before the button changes color at the very end. So, except for having a colorful button that I don't want at the end, it isn't accomplishing what I want it to do. Here's the code I've tried with the attempt commented out. Is it possible to do what I'm hoping for and if so, what am I doing wrong. I'm a novice with VBA so I try and find someone else's code that does what I want and copy and modify it to work on my workbook. Here's what I've got for this function.

Please Login or Register  to view this content.

Hello OhioChip83,


Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)