Excel - Create a punch clock - additional question

mchapman286
mchapman286 used Ask the Experts™
on
I asked a question yesterday about creating a punch clock spreadsheet in Excel and saurabh726 was kind enough to share a spreadsheet that they created.  The spreadsheet is awesome, but I'm not sure how to recreate the button commands in a blank copy of Excel.  I know how to add a button in Excel, but not how to add the VBA code to the button.

Thanks for your help
File-Timer.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ardhendu SarangiSr. Project Manager

Commented:
Hi,

To copy the code over to a new sheet, first use ALT + F11 to open the VBA Editor. Copy the code that you see from the old workbook. Then go to the desired workbook, click on Insert...Module to create a blank module sheet. Paste the code there, then ALT + F11 to return to the spreadsheet.

To run the code, hit ALT + F8 to open the macro window. Select the macro, then click the Run button.

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK."    

Thanks,
Ardhendu

Author

Commented:
Thanks Ardhendu, I know how copy the code into a new Microsoft Excel Project, but don't know how to get it to link to the button when the button is pressed.  It does not look like it is a macro assigned to the button.  Whe you look at the file you can not or at least I can not see any macros when I click on Tools, Macros then on Macros.  I also can not move the button in the worksheet so maybe it's not a typical button?  I'm probably missing something so simple.  
Senior Risk Manager
Commented:
mchapman,

Go to View->Toolbars->Control Toolbox

Click the upper left icon on the toolbar to enter Design Mode.  This allows you to select the control.  Also from this toolbox you can "View Code" which will take you to the controls Click Event.  ActiveX controls use Events, not the "Assign Macro" like the controls from the Forms toolbar.

Cheers,
WC
Cory VandenbergSenior Risk Manager

Commented:
Oh, and as far as adding more code for the control.

Once you are in the VBE.  Say you double-clicked on the control while in Design Mode which took you to the Click Event code.  In that code window, there are two dropdowns at the top.  The one on the left for selecting the object you want to work with, and one on the right for selecting the Event you want.

They should be on "cmdTimer" and "Click" respectively.  Change the Click Event to something else, and you'll notice a shell block of code is inserted for that Event.

WC

Author

Commented:
Thanks!  I have been able to recreate the command button into my worksheet.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial