Auto Hide/Unhide Rows Anytime Drop Down List Changes

I'm very new to macros. I have an income statement by month with an ActiveX Combobox containing the various departments. On a separate sheet called "Analysis" (in the same format minus the drop down list) is calculating % of revenue. In column P is a formula to give me "1" if any month (Jan-Dec) contains a cost that is greater than 5% and "0" if not. I want to see every row(cost) that contains 5% or greater. I want all of the rows within my range to hide or unhide anytime the user makes a new selection from the drop down list. I have some codes that work but not automatically. Every time I make a different selection I have to open up the vba and hit F5 to run it. Here are 2 codes I've tried and work but do not auto hide/unhide when a new selection from drop down list is made. Any help will be GREATLY appreciated.

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

BeginRow = 22
EndRow = 300
ChkCol = 16

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
ElseIf Cells(RowCnt, ChkCol).Value = 1 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

----------------------------------------------------------------------

Private Sub Worksheet_Activate()
If MyVal = "" And [B11] <> "" Then MyVal = [B11] & ""
End Sub


Private Sub Worksheet_Calculate()
Dim CELL As Range


If [B11] <> MyVal Then
For Each CELL In Range("P22:P300")
Rows(CELL.Row).Hidden = CELL = 0
Next CELL
MyVal = [B11] & ""
End If

End Sub

Hi Jide,

Welcome to the Forum!

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

BTW:

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

Hi jkle4596

Welcome to the Forum!!!

Place this Code in the Sheet Module for Monthly Trend (Right Click on the Tab ---> View Code ---> Paste the Code into the Right Hand Window.

Each time the ConboBox is changed the Code will fire. The Code Filters on Column P of Sheet Analysis (displays only the 1's).

Please Login or Register  to view this content.

You're welcome...glad I could help. Thanks for the Rep.

Hi jkle4596

Try this...

Please Login or Register  to view this content.

Hi jkle4596

Add a couple lines of Code...

Please Login or Register  to view this content.

See Post #11

Hi jkle4596

The Code you presented only runs on Workbook Open...never again until after Workbook is Closed and then re-opened. So, no, they are not in conflict.

What flicker I see is negligible. The only thing I can recommend is Activating the Results Sheet (Analysis) because that's what "management" expects to see...sorry...

Please Login or Register  to view this content.

Or, try this, see if it's any better...

Please Login or Register  to view this content.

Hi jkle4596

Sure, try it. If you need help please explain, specifically, what you have in mind.