Hide checkbox when formula returns ""

[ungers]

I have sheet made of values returned by formulae and a cheeckbox per row to be able to select which rows the user wants to use.

I want to be able to hide the checkboxes when the formula for that row returns "".

The event needs to run everytime the sheet recalculates, there is no manual entry on that sheet.

I've tried things similar to below but i'm not sure i'm even close.

Please Login or Register  to view this content.

If anyone can give some pointers i'd be very grateful for the assistance.

[walruseggman]

Try

Please Login or Register  to view this content.

[LJMetzger]

Hi ungers,

You are very close and very far away at the same time.
a. The Value of cell 'C36' is probably 0, even if BLANK is displayed. To get around this you probably have to test the Text value of the cell and remove leading and trailing spaces with the Trim() function.

Please Login or Register  to view this content.

b. The remainder of your code works, but is going to get out of hand due to repetition with several CheckBoxes. You probably need a naming convention for your CheckBoxes to be able to do your testing in a loop. I like to use names like 'Check Box F13' to indicate a Forms CheckBox (Forms CheckBoxes are created with spaces in the name) located in cell 'F13'. Using a naming convention like that, you can use the following routines (tested and working) during software development to create and delete your CheckBoxes. Additional macros are included to trap CheckBox events and to display or hide the CheckBox per your original question.

In an ordinary Code Module:

Please Login or Register  to view this content.

In the Sheet module include the following code:

Please Login or Register  to view this content.

Lewis

[ungers]

I've made progress with the following only my loop only seems works on the first row?

Please Login or Register  to view this content.

I assume a silly mistake somewhere but i can't find it!

[LJMetzger]

Hi,

Your code indicates that your CheckBox names start at "Check Box 101" and you start in cell 'B4'.

Your main problem was that the line in red has to be inside the loop.
It is also possible that your value test would always indicate false.

Try this.

Please Login or Register  to view this content.

[ungers]

That's great thank you. As soon as I moved that line inside the loop it worked fine.

A further thing though I have a select all checkbox, but I think because I have used Sub Worksheet_Calculate() above it takes ages to work as I think it is recalculating everytime a checkbox is changed.

Is there anyway I can refine when the code runs or temporarily disable it with the select all macro?

[LJMetzger]

It probably is recalculating. The following changes in red should improve speed performance:

Please Login or Register  to view this content.

Lewis

[ungers]

I just moved this code to the sheet (called "results") where all the selections are made as the sheet "bank summary" is really just a display.

I also added in the following 2 lines to reset all the checkboxes when the underlying data is changed or the user makes a different selection.

Please Login or Register  to view this content.

This works fine but now whenever I try to check a box it is also triggering the results sheet worksheet calculate event and therefore resetting them to false again. If I comment out these two lines it works fine. There is no connection between these sheets.

So why is an action on one sheet triggering the event on the other?

[LJMetzger]

I think there are probably 2 problems:

a. In the Sheet module for the "BANK SUMMARY" Sheet the code works, but should be (changes in red):

Please Login or Register  to view this content.

b. You probably copied the sheet, and there is Worksheet_Calculate() code for the sheet. Worksheet_Calculate() should probably be deleted on the new sheet. The Worksheet_Calculate() on the new sheet as it currently exists, probably references sheet "BANK SUMMARY" and is probably the reason for the problems.

Lewis

[ungers]

I made a mistake and there is a two way relationship between the two sheets so that's why it was re-calculating.

I got round this by making a new sheet with a single cell based on the radio button selection and used Worksheet_Calculate() and moved the follfowing there to reset the sheet.

Please Login or Register  to view this content.

Thanks for all you assistance.