Making Rectangles or Checkboxes Appear as Needed

[bmasella]

I have a worksheet that I use as an Agenda. (picture attached below)

The column that has the Agenda Item 1, Agenda Item 2, etc... is actually a formula that is grabbing data from other areas of my workbook. As you can see I currently just have a rectangle drawn in the column labeled complete. Since the formula used to create the agenda items may or may not produce a value for the row what I would like to do is not have the rectangle appear if the value of the cell to the left of it is blank.

It was also suggested that I could use the checkbox object and if I did that it could populate the cell to the right with the date that the check box was activated. That would be helpful as well but I would still only want the checkbox object to appear if there were something in the Areas of Discussion Column.

I'm thinking it is going to be VBA of some kind that will need to accomplish this. Anyone have any code suggestions?

The picture of what my spreadsheet looks like... agenda.jpg

[bmasella]

Perhaps conditional formatting of some kind to change the color of the rectangle to the same as the background?

[LJMetzger]

Hi bmasella,

Try the attached file tested using Excel 2003. This should work for your version of Excel.

Installation Instructions (to copy code to your workbook):
Make a backup copy of your file before starting.
a. Open this file and access VBA.
b. Open Your Workbook.
c. Export Module 'ModFormsCheckBoxes' from this Workbook.
d. Import Module 'ModFormsCheckBoxes' to your workbook.
e. Export UserForm 'UserFormCheckBoxCentral' from this Workbook.
f. Import UserForm 'UserFormCheckBoxCentral' to your Workbook.
g. Copy the code from 'Sheet1' module from this Workbook to the clipboard.
h. Paste the code from 'Sheet1' to the code for YOUR SHEET.
i. Modify the value of constant 'sCheckBoxCompleteTextCELL' in Module 'ModFormsCheckBoxes' in your workbook to contain the location of the 'Complete' Header (e.g. 'F17').
j. Save your Workbook. Installation complete.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/

To import or export VBA code:
a. To export, right click on the Module Name in the 'Project Explorer'.
b. Select export file. I suggest you use a SubFolder that only contains exported (.bas) files.
Keep the original name.
c. To import, right click anywhere in 'Project Explorer'.
d. Select import file. Select a file to import.

Lewis

[bmasella]

Wow - Thanks... I think this does what I want but in playing with your worksheet I have a few questions...

I just want it to be automatic I don't want/need a user form to display them or take them away. If there is something in areas of discussion make the checkbox appear. There will always be 26 rows always starting at a specific row so I should be able to have a begin and end range and of the 26 rows there will be any number of them that will have a value in the area of discussion.

When I click on the checkbox in your example worksheet it (the checkbox) disappears and a date appears. I'd like to see the checkbox with the check. Could be that I'm using 2010?

Great example code for me to learn from.

[LJMetzger]

Hi,

Everything works the way it was designed to work. Why don't you post a sample workbook, with the 'Areas of Discussion', 'Completed', and 'Date' headers where they're supposed to be.

That way I can customize the codes to your needs

The updated software will:
a. Have no userform
b. Checkbox will always be visible (except when 'Areas of Discussion' is blank)
c. A 'Master' Worksheet that will act as a template, containing the 26 rows with checkboxes that can you can cut and paste to new worksheets, or copy as is.
d. Anything else you want or can remove anything you don't want.

Lewis

[bmasella]

I will send you the worksheet... thanks.

[bmasella]

Worksheet.

[LJMetzger]

Hi,

Test the attached file tested using Excel 2003. You have to install the macros into your file because, my workbook is NOT COMPATIBLE with your Excel 2010, due to:
a. use functions that are not available in Excel 2003
b. use of External links (that I don't have) to refresh the data

To simulate the external links, I linked the 'Areas of Discussion' column in my workbook to Column J. Enter a value in Column 'J' in my workbook to simulate external data. Type directly in the 'Areas of Discussion' Column to enter data directly (which will destroy the simulated External Link).

I was unable to duplicate the COLOR of your RECTANGLE, due the limitations of either Forms Checkboxes or my skill level. Right now you can have any color you want as long is the color is black.

-----------------------
Installation Instructions (to copy code to your workbook):

Because 'Areas of Discussions' is updated by external formula, only 'Sheet1' can contain CheckBoxes.

Make a backup copy of your file before starting.
a. Open this file and access VBA.
b. Open Your Workbook.
c. Export Module 'ModFormsCheckBoxes' from this Workbook.
d. Import Module 'ModFormsCheckBoxes' to your workbook.
e. Copy the code from 'ThisWorkbook' module from this Workbook to the clipboard.
f. Paste the code from 'ThisWorkbook' to the code for YOUR SHEET.
g. Copy the code from 'Sheet1' module from this Workbook to the clipboard.
h. Paste the code from 'Sheet1' to the code for YOUR SHEET.
i. Modify the value of constant 'sCheckBoxCompleteTextCELL' in Module 'ModFormsCheckBoxes' in your workbook to
j. Run Macro 'InitializeSheetWithCheckBoxes' to delete RECTANGLES, and install CHECKBOXES.
k. Save and close your Workbook. Installation complete.

To access Visual Basic (VBA) see:
http://www.ablebits.com/office-addin...a-macro-excel/

To import or export VBA code:
a. To export, right click on the Module Name in the 'Project Explorer'.
b. Select export file. I suggest you use a SubFolder that only contains exported (.bas) files.
Keep the original name.
c. To import, right click anywhere in 'Project Explorer'.
d. Select import file. Select a file to import.

Lewis

[bmasella]

Absolutely PERFECT. Thanks. Since I had more than 1 sheet in my massive workbook I made a couple of modifications that allowed me to use the code on multiple worksheets. Your effort is greatly appreciated.

[LJMetzger]

I'm glad it worked out for you. Silly me. I should have coded it so that each time a worksheet was activated, the current values of 'Area of Discussion' for that sheet were stored in the 'old values' array.

Please let me know if you have any problems with this in the future.

Lewis