Another VBA question to hide/unhide rows based on cell value in another sheet

[bertible]

I've reviewed many responses on this question, both on MrExcel and elsewhere, and just can't seem to get my hide-rows macro to run in Excel "LTSC":

I've tried a few syntax alternatives to the following "Case" method, but it just won't run (I did have it running in the module as a manual macro but not in the worksheet by a cell change). You will see two versions here, the second inactive one commented with leading '. (I've tried many different syntaxes). The range name TBOEx is a single cell j24 on sheet 2 (with a pulldown Yes,No validation). This is typical in my spreadsheet designs, to have a parameters page in which the user selects options, then a series of separate results pages. The rows I wish to hide when there is not data (Case "No") and to show in case "Yes", are on two other sheets, 12&13. i have a separate Case VBA Sub on this Sheet2 to hide and show complete worksheets and that one works fine from another pulldown cell. I've saved and reopened, rebooted, etc

Private Sub HideRowsTBOEx(ByVal Target As Range)
'added below 2 lines per recommendation of several  forums
TBOX = Worksheets("Sheet2").Range("TBOEx").Value
If Intersect(TBOX, Target) Is Nothing Then Exit Sub

Select Case TBOX
        Case "No"
            Worksheets("Sheet12").Rows("36:48").Hidden = True
            Worksheets("Sheet3").Rows("36:48").Hidden = True
        Case "Yes"
            Worksheets("Sheet12").Rows("36:48").Hidden = False
            Worksheets("Sheet3").Rows("36:48").Hidden = False
   End Select
End Sub

'Inactive version
'Sub HideRows(ByVal Target As Range)
'macro did run using F5 in module but not here in sheet2 and not yet automatic on TBOEx change

'Select Case Sheet2.Range("j24").Value
        'Case "No"
            'Sheet12.Range("36:48").EntireRow.Hidden = True
            'Sheet3.Range("36:48").EntireRow.Hidden = True
        'Case "Yes"
            'Sheet12.Range("36:48").EntireRow.Hidden = False
            'Sheet3.Range("36:48").EntireRow.Hidden = False
   ' End Select
'End Sub

 

[kevin9999]

are on two other sheets, 12&13

Worksheets("Sheet12").Rows("36:48").Hidden = True
Worksheets("Sheet3").Rows("36:48").Hidden = True

I'm guessing you mean sheet 13? If not, you can change the sheet reference. Please try the following in the sheet module of your sheet with the Yes/No option.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Range("J24"), Target) Is Nothing Then Exit Sub
    If Target = "No" Then
        Worksheets("Sheet12").Rows("36:48").Hidden = True
        Worksheets("Sheet13").Rows("36:48").Hidden = True
    ElseIf Target = "Yes" Then
        Worksheets("Sheet12").Rows("36:48").Hidden = False
        Worksheets("Sheet13").Rows("36:48").Hidden = False
    End If
End Sub

 

[Peter_SSs]

@bertible
Welcome to the MrExcel board!
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[bertible]

Thanks for quick response, but still flummoxed. I pasted an exact copy of your code over mine and changed sheet13 to 3, as 3 is the correct number. It also said that Worksheet_Change was a vague name so I changed it to Worksheet_Change1. No other changes to your code and it didn't work any differently.

I then tried one more thing in case the target cell needs to have its sheet defined (even though the Code is in Sheet2.)

If Intersect(Sheet2.Range("J24"), Target) Is Nothing Then Exit Sub

No effect. Hoping for some other ideas.

By the way here's the one other Sub in that same sheet that works fine using cell j4 as a toggle.

Private Sub Worksheet_Change(ByVal Target As Range)
'Application.Volatile

Select Case Sheet2.Range("j4").Value
        Case "Summary"
            Sheet12.Visible = xlSheetVisible
            Sheet5.Visible = xlSheetVeryHidden
            Sheet3.Visible = xlSheetVeryHidden
        Case "Detail"
            Sheet12.Visible = xlSheetVeryHidden
            Sheet5.Visible = xlSheetVisible
            Sheet3.Visible = xlSheetVisible
   End Select
End Sub

Gary(bertible)

 

[kevin9999]

The code is a worksheet change event macro so: 1. Do NOT change the name 2. The code must be put in the worksheet code module of the sheet where the change takes place - right-click the sheet tab name, select View Code & paste the code into the window that appears on the right of the screen. Save as a macro-enabled file.

 

[kevin9999]

The problem is that you can't have 2 worksheet change code routines on the same sheet. You can, however, get the code to do multiple things depending on which cells change. Please replace your existing code (the one that you say "works fine") with the following. I note you're using the sheet code names in your code and not the sheet tab names so that's the way I've left it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("J4,J24"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        
        If Target.Address = "$J$4" Then
            If Target = "Summary" Then
                Sheet12.Visible = xlSheetVisible
                Sheet5.Visible = xlSheetVeryHidden
                Sheet3.Visible = xlSheetVeryHidden
            ElseIf Target = "Detail" Then
                Sheet12.Visible = xlSheetVeryHidden
                Sheet5.Visible = xlSheetVisible
                Sheet3.Visible = xlSheetVisible
            End If
        End If
        
        If Target.Address = "$J$24" Then
            If Target = "No" Then
                Sheet12.Rows("36:48").Hidden = True
                Sheet3.Rows("36:48").Hidden = True
            ElseIf Target = "Yes" Then
                Sheet12.Rows("36:48").Hidden = False
                Sheet3.Rows("36:48").Hidden = False
            End If
        End If
        
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[bertible]

Ah Ha. This is something I haven't tried, and I started thinking about it when I saw that my prior sub was called Worksheet_Change (which is why it gave me an error on the 2nd sub. I'll try combining them into one per your example.

Gary

 

[bertible]

WooHoo! Works perfectly. I probably wouldn't have thought of putting both subs in one. But I've now tried all possible combinations of the two cells and it works great!

Thank You kevin9999

 

[kevin9999]

Happy to help, and thanks for the feedback

 

[bertible]

Hi Kevin et al,

I was happy about the solution above, but I realize now that I had not yet taken the very last step before I release this workbook to users. The workbook requires Users to input data in protected sheets in unlocked cells, and there are some additional fully protected output sheets they can view and print. To allow macro's to run in a protected file, I had to protect all worksheets, but not the workbook. Then I protected the VBA project to disallow access to the VBA code. With the fix above, I am now having problems with the hide rows code added per above.