Refilter if the value of a linked cell from a form control is changed

[ElBB23]

Hi all,

Having done a similar VBA code before I have tried to do this myself but I am struggling.

On sheet two I have a drop-down box that I inserted via the form controls (developer tab). The drop-down box has been linked to cell c17 in sheet one.

Cell c17 in sheet one forms part of a range of information and depending on what is selected in the drop-down boxes this data will change. This can change from one line of data to 8. This links to a pie chart that also updates based on the selection in the drop down dox.

The problem is that the pie chart data range pulls through all 8 lines of the table but, when an option is selected in the dropdown box that only has one line of information the pie chart shows 7 sets of zero data. If I add a filter and uncheck the "blanks" option I can remove these.

So, I've tried to put VBA together that when the value of c17 in sheet one changes it refilters the data table to remove the blanks but shows all valid information. Try as I might it's not working.

Any help would be appreciated, I been searching for hours. I apologise if I've not explained it very well

 

[James006]

Hi,

Quite honestly ... your explanation is extremely confusing ...

May be ... in your Sheet1 module you can add following event macro :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$C$7" Then Exit Sub
' Insert below your code to Filter your data


End Sub

If this does not work ... you could test the Private Sub Worksheet_Calculate() event ...

Hope this will help