Hide Column on Another Sheet

LPalmer

New Member
Joined
Oct 8, 2019
Messages
12
Hello All,

I have the macro below that I was hoping to get some assistance on. Macro was working fine until I tried to both hide/unhide rows on the current worksheet and hide/unhide column on another worksheet based on response given in cell C24. Feel like it is something simple with the end if statements but just not getting it. Thanks!

Code:
Private Sub Worksheet_Change(ByVal Target As Range)   If Target.CountLarge > 1 Then Exit Sub
   
   If Target.Address(0, 0) = "C8" Then
      'Message Box Appears or Unhides Rows if Value is less than 25
      If Target < 25 And Target <> "" Then
         Rows("10:11").EntireRow.Hidden = False
         MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
         
      'Hides Rows if Value is equal to or greater than 25
      ElseIf Target >= 25 Or Target = "" Then
         Rows("10:11").EntireRow.Hidden = True
        End If
         
    ElseIf Target.Address(0, 0) = "C12" Then
      'Hides Rows if Cell C12 equals "No"
      If Target = "YES" Then
         Rows("13:15").EntireRow.Hidden = False
      ElseIf Target = "NO" Then
         Rows("13:15").EntireRow.Hidden = True
         End If
         
'Why Don't you give Task 1C and Task 1D a shot!
         
     ElseIf Target.Address(0, 0) = "C24" Then
      'Unhides rows if C24 says "Yes"
      If Target = "YES" Then
         Rows("25").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         With Worksheets("2)Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = False
         End With
         
      If Target = "NO" Then
         Rows("25").EntireRow.Hidden = True
         Rows("26").EntireRow.Hidden = True
         With Worksheets("2)Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = True
         End With
         End If
         
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,844
Office Version
2007
Platform
Windows
An End If is missing:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge > 1 Then Exit Sub
   
   If Target.Address(0, 0) = "C8" Then
      'Message Box Appears or Unhides Rows if Value is less than 25
      If Target < 25 And Target <> "" Then
         Rows("10:11").EntireRow.Hidden = False
         MsgBox "Unless the plan has less than 250 participants, it would be unusual to have a sample size of less than 25. Verify that this is the sample size that was determined to be appropriate."
         
      'Hides Rows if Value is equal to or greater than 25
      ElseIf Target >= 25 Or Target = "" Then
         Rows("10:11").EntireRow.Hidden = True
      End If
         
  ElseIf Target.Address(0, 0) = "C12" Then
      'Hides Rows if Cell C12 equals "No"
      If Target = "YES" Then
         Rows("13:15").EntireRow.Hidden = False
      ElseIf Target = "NO" Then
         Rows("13:15").EntireRow.Hidden = True
      End If
         
'Why Don't you give Task 1C and Task 1D a shot!
         
  ElseIf Target.Address(0, 0) = "C24" Then
      'Unhides rows if C24 says "Yes"
      If Target = "YES" Then
         Rows("25").EntireRow.Hidden = False
         Rows("26").EntireRow.Hidden = False
         With Worksheets("2)Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = False
         End With
[COLOR=#ff0000]      End If[/COLOR]
      If Target = "NO" Then
         Rows("25").EntireRow.Hidden = True
         Rows("26").EntireRow.Hidden = True
         With Worksheets("2)Participant Eligibility")
            .Columns("F:F").EntireColumn.Hidden = True
         End With
      End If
[COLOR=#ff0000]  End If[/COLOR]
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
7,844
Office Version
2007
Platform
Windows
I'm glad to help you. Thanks for the feedback.
 

Forum statistics

Threads
1,078,216
Messages
5,338,907
Members
399,267
Latest member
Danielle1017

Some videos you may like

This Week's Hot Topics

Top