Assigning F9 or shiftF9 to a macro

[rob1987]

Hello,

I have the bulk of my code sorted but I am just missing the last detail. In Sheet2 I have columns of data, split into ranges by month name, Feb and March . In the last column, T, I have the total of the values within each row. Simple so far. In the sheet Overview I have two checkboxes to hide and unhide the month ranges, one with the macro:

Range("Feb").EntireColumn.Hidden = Not Range("Feb").EntireColumn.Hidden

and the second with the same thing for the March range.

I then want T to update to show the values for only the non hidden rows. To do this I entered the following code into the sheet module:

Public Function SumVisible(myRng As Range)
Dim myCell As Range, mySum As Double
Application.Volatile
For Each myCell In myRng
    If myCell.ColumnWidth <> 0 Then mySum = mySum + myCell.Value
Next myCell
SumVisible = mySum
End Function

and in column T used the =SumVisible(f3:m3) forumla which works fine. My only issue is that I need to press f9 each time I use my check boxes to hide/unhide the ranges. Is there anything I can build into my code anywhere to automatically calculate the new total whenever I use the checkboxes?

 

[Andrew Poulsom]

You could try:

Application.CalculateFull

 

[CWatts]

Perhaps this would work?

Application.SendKeys ("^{F9}")

 

[shg]

In the last line of the macro, you could add (after hiding or unhiding the column)

Columns("T").Calculate

Then you could remove the Application.Volatile from the UDF.

 

[rob1987]

Thank you all for your replies.

I tried Andrew's recommendation as the first one and it has worked perfectly, so thank you for that, but thanks all for your recommendations.