Change Event - Hide Range

[Hainesy]

Hi all,

I have the following VBA code that I am having issues with, ive pieced it together from various sources but it doesn't quite work as it should...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
 
If Not Intersect(Range("H6"), Target) Is Nothing Then
  Select Case UCase(Range("H6").Value)
  
    Case ""
        Rows("9:32").Hidden = True
                
    Case "A"
        Rows("9:19").Hidden = True
      
    Case "B"
        Rows("20:32").Hidden = False
    
  End Select

End If

Application.ScreenUpdating = True
 
End Sub

Basically H6 will only be either A, B or Blank.

Depending on the selection in H6, certain rows need to be visible.

H6 by default will be blank, and rows 9:32 need to be hidden from the off.

Any help would be appreciated and I am sure this will be a quick fix for someone!

Thanks in advance!

 

[Robby19]

Could the issue be that the above VBA is looking for a LOWERCASE a OR b, however in the OP data list, it is an UPPERCASE A and B?

 

[Joe4]

H6 is currently merged with I6, could this be an issue?

Try unmerging it, and see if it then works as expected.

Note that merged cells are VERY problematic for things like VBA and sorting, and should really be avoided.
You can achieve the similar visual effect without all the problems by using the "Center Across Selection" formatting option instead of merging cells.
See: https://www.atlaspm.com/toms-tutori...er-across-selection-instead-of-merging-cells/

 

[Hainesy]

Hi all,

The case of the text matched the code so that wasn't it.
I have tried it merged and unmerged but that didn't make any difference either.

the code in its current form, that is attributed to a button currently is:

Sub PIMSACCESSLEVEL()
Dim al As String
al = Range("e8")

ActiveSheet.Unprotect
Application.ScreenUpdating = False

        Rows("10:31").Hidden = True

If al = "UW" Then
        Rows("10:14").Hidden = False
        Rows("19:31").Hidden = False

ElseIf al = "Admin" Then
        Rows("10:31").Hidden = False
        Rows("10:30").Hidden = True
        Range("c13,c17,e17,c21,c25").Select
        Selection.ClearContents

ElseIf al = "Read Only" Then
        Rows("10:31").Hidden = False
        Rows("10:30").Hidden = True
        Range("c13,c17,e17,c21,c25").Select
        Selection.ClearContents

ElseIf al = "" Then
        Rows("10:31").Hidden = True
        Range("c13,c17,e17,c21,c25").Select
        Selection.ClearContents
        MsgBox "Enter a PIMS Access Level"

End If

        Range("e4").Select
        ActiveWindow.SmallScroll Down:=-200
        ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub

This works flawlessly on the button, but will not work at all as a change event!

Please help!

Thanks in advance.

 

[Joe4]

Please post your version of the Worksheet_Change code that you are trying.

 

[Fluff]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Intersect(Target, Range("E8")) Is Nothing Then Exit Sub
   
   ActiveSheet.Unprotect
   Application.ScreenUpdating = False
   Application.EnableEvents = False
   
   Rows("10:31").Hidden = True

   If Target.Value = "UW" Then
      Rows("10:14").Hidden = False
      Rows("19:31").Hidden = False
   ElseIf Target.Value = "Admin" Then
      Rows(31).Hidden = False
      Range("c13,c17,e17,c21,c25").ClearContents
   ElseIf Target.Value = "Read Only" Then
      Rows(31).Hidden = False
      Range("c13,c17,e17,c21,c25").ClearContents
   ElseIf Target.Value = "" Then
      Range("c13,c17,e17,c21,c25").ClearContents
      MsgBox "Enter a PIMS Access Level"
   End If
   
   Range("e4").Select
   ActiveSheet.Protect
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub