VBA Script Needed to Hide Checkboxes Based on Column Values

Hi,

I am new to posting on the forum and not a regular user of VBA. I have reviewed several other similar posts and tried to implement those solutions, but have not been successful. My worksheet contains the following:

- Column G includes fifteen rows of a formula (beginning at G9) that returns a dynamic list of values based on criteria chosen through a dropdown box. The list can be anywhere from five to 20 values depending on the selection. When the potential matching values for the list are exhausted, the formula returns ""
- Column F includes one checkbox for each of the 20 rows containing the formula.

I need a script that will hide each checkbox that is next to a "blank" value. That is, if the cell in Column G = "", I need the corresponding checkbox to be invisible. Unfortunately, hiding the entire row is not an option. If the script could uncheck the boxes when hidden, that would be a bonus, but isn't absolutely necessary.

Thanks!

Hi,

The attached file should do what you want but the CheckBoxes MUST BE named 'CheckBox1' thru 'CheckBox20'.
If they are not named as such the following two Macros (also in the file) should help you rename them.

This macro displays information about all 'Shapes' on the active sheet (including CheckBoxes):

Please Login or Register  to view this content.

This macro renames Shapes:

Please Login or Register  to view this content.

The following macros:
a. Display all CheckBoxes
b. Do the CheckBox manipulation you requested.

Please Login or Register  to view this content.

Lewis

Hi Lewis,

Thank you so much! This gets me very close. The only rub is that I need the script to run any time there is a change in the values in Column G. Can I insert a line to this effect in the script you have provided? The list of values in column G increases and decreases in length based on criteria selected from a drop-down box, so the script needs to run any time there is a change in column G values. Does this make sense?

Again, many thanks for a great solution.

Hi again,

I think the attached file does what you want and incorporates all the following code. You should probably delete the macros from the previous solution and replace them with the new macros. The new solution requires:
a. Workbook_Open() to store the original values in column G. This is needed when a formula changes one or more of the values in Column G.
b. Worksheet_Calculate() to update the CheckBoxes when a formula changes one or more of the values in Column G.
c. Worksheet_Change() to update a CheckBox when the corresponding value in Column G changes.

This incorporates the ability for a cell in another Sheet to change the value in Column G via formula.

VERY IMPORTANT - It is ASSUMED that the 'Column G' data is on "Sheet1". If this is NOT the case, then the value of 'sSheetForColumn_G_RANGE' on line 4 of Module 'ModCheckBoxes' MUST BE CHANGED to the name of the proper SHEET.

I tested the code pretty thoroughly. Please let me know if you have any problems or questions.

Lewis


Code in Module 'ThisWorkbook':

Please Login or Register  to view this content.

Code in Module 'Sheet1':

Please Login or Register  to view this content.

Code in Module 'ModCheckBoxes':

Please Login or Register  to view this content.

Code in Module 'ModShapes'. This is needed if the 'CheckBox' names are not 'CheckBox1' thru 'CheckBox20' as stated in my previous post:

Please Login or Register  to view this content.

This is outstanding. The solution works perfectly and I appreciate the time you put into it. Many many thanks!