Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checklist

**trans9_5am** · 03-05-2014, 03:40 PM

Hello all-

I am new to the forum but very excited that I found you guys!! There have been several times in the past where I was in search for Excel help and most resulted in the lack there of. It looks as though there is plenty of activity on this site so I am hoping for a resoultion to my request.

I have attached a Standard Work document that i have been working on. If you open the document, you will notice that I am using VBA to insert a "O" in the proper cell where a given task will be performed on a given day. Instead of a "O", I would like to manage the document fully electronically and was leaning towards the ActiveX Control -> Check Box. The functionality of the form must remain the way it is now....i just want to use the ActiveX Check Box so that I can open the document, check the tasks that have been completed that day, and then save the document until I reopen it the following day.

Any help or suggestions would be appreciated.

Thanks,

B

**Leith Ross** · 03-05-2014, 03:50 PM

Hello trans9_5am,

Welcome to the Forum!

I suspect you will have more than one sheet like "Sheet1" in the workbook. Using that many controls will increase the size of your workbook quite a bit and possibly slow it down. The controls will not retain their states once the workbook is closed.

A better option would to use a Double Click on the cells in the columns where the O's are now. There would be no added overhead and the worksheet will retain the cell values when the workbook is closed.

**Richard Buttrey** · 03-05-2014, 04:05 PM

Hi,

Are you sure you really want a macro for this? The current looping macro takes a relatively long time to process and is unnecessary given that there are perfectly good excel functions that you could use to populate F3:AJ29. Then changes would be reflected instantaneously instead of having to click a button which then takes a minute or two to process.

Unless I've missed something I'm not sure what simply adding activex controls brings to the party unless you're going to use them for some other purpose which is not yet clear. Apart from that actually positioning the controls would require additional code to cater for the differing row heights. Not un-doable but arguably unnecessary.

If it's just a question of marking the O in some way, i.e. akin to ticking a checkbox, then a simple sheet double click event or similar could paint the cell a different colour.

**Leith Ross** · 03-05-2014, 04:15 PM

Hello trans9_5am,

Here is the event code that has been added to the attached workbook Double click a cell in columns "F:AJ" and "O" will be inserted if the cell is empty. If an "O" is already in the cell, it will be cleared.

Sheet1 Double Click Event Code

Please Login or Register  to view this content.

**trans9_5am** · 03-06-2014, 01:45 PM

Leith,

I believe your solution will work for what I 'm after. This is a function that I was completely unaware of. Thank you for both a quick and intelligent response!!!

I was wondering if along with the double click to clear the cell....could I shade the cell green? Just as a visual indicator that the task did exist, but was completed. I altered your code jus slightly to place an "X" instead of clearing the cell. It would just be a nice visual to shade the cell as well.

This site is awesome! I will definitely recommend to anyone in the future that is looking for quick responses to Excel questions.

Thanks again,

B

**Leith Ross** · 03-06-2014, 03:08 PM

Hello trans9_5am,

Here is the updated macro to add the "X" and color and remove it.

Please Login or Register  to view this content.

Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checklist

LinkBack

Thread Tools

Rate This Thread

Display

Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checklist

Re: Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checkl

Re: Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checkl

Re: Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checkl

Re: Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checkl

Re: Would like to use VBA/Macro to insert Active X "Check Box" into a Standard Work Checkl

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] EXCEL - Match Name, Sort, Check if "Active"

"Form and macro" to delete "Active" cell contents.

[SOLVED] Macro to look up and down from the active cell, copying the active "section" of the sheet

Macro Error with code "Range("A65536").End(xlUp).EntireRow.Insert"

How HardDisk neme "N" or "?" (as cell value) insert into macro formula?

Bookmarks

Bookmarks

Posting Permissions