+ Reply to Thread
Results 1 to 10 of 10

Making Rectangles or Checkboxes Appear as Needed

  1. #1
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155

    Making Rectangles or Checkboxes Appear as Needed

    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

  2. #2
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155

    Re: Making Rectangles or Checkboxes Appear as Needed

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

  3. #3
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Making Rectangles or Checkboxes Appear as Needed

    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
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155

    Re: Making Rectangles or Checkboxes Appear as Needed

    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.

  5. #5
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Making Rectangles or Checkboxes Appear as Needed

    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

  6. #6
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155

    Re: Making Rectangles or Checkboxes Appear as Needed

    I will send you the worksheet... thanks.

  7. #7
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155

    Re: Making Rectangles or Checkboxes Appear as Needed

    Worksheet.
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Making Rectangles or Checkboxes Appear as Needed

    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

  9. #9
    Forum Contributor
    Join Date
    02-27-2007
    Posts
    155

    Re: Making Rectangles or Checkboxes Appear as Needed

    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.

  10. #10
    Forum Guru
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,859

    Re: Making Rectangles or Checkboxes Appear as Needed

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Making checkboxes not visible
    By Elizabethd1012 in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 05-01-2014, 02:27 PM
  2. Making One Checkbox Check Other Checkboxes plus more
    By zapatam21 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-19-2013, 02:19 PM
  3. Auto making of checkboxes
    By stojko89 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-12-2010, 05:35 AM
  4. [SOLVED] Making checkboxes mutually exclusive
    By instructorjml in forum Excel General
    Replies: 3
    Last Post: 04-06-2006, 01:50 AM
  5. Making a list box of checkboxes
    By David Gerstman in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-07-2005, 06:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1