Help needed with VBA code

[PGD15]

Hi,

I am currently looking to write some VBA code to say if a certain cell equals a certain value then hide a row otherwise this row should be displayed.

E.g. If cell value L92 is populated with the value Yes, then I want rows 95 & 96 to be on show. If L92 is blank/empty or filled with any value other than yes (<> yes) then i want rows 95 & 66 to be hidden. (I know its possible to change the row heights accordingly but I do not know how).

If you are able to help by posting the code, can you please include 'explaining what each part means Thanks!

 

[PGD15]

RoryA this picture name is Picture 39.

Thanks for the help

 

[PGD15]

Mumps if I could I would but due to the nature of the document it contains sensitive information and I would lose my job if i uploaded the file :'( however i might try and remove that information then see if i can do it that way so only the required part would be worked on

 

[PGD15]

Mumps

Try this link - i've removed all the information and macros i currently have the tab you need is called form so ideally when the drop down box value says yes i want the rows below to automatically become visible and if it doesn't say yes to hide without running a button.


https://ibm.box.com/s/m1wtefcdhyco5355xg2brsjh5ahde3mb

 

[Rhodie72]

Mumps if I could I would but due to the nature of the document it contains sensitive information and I would lose my job if i uploaded the file :'( however i might try and remove that information then see if i can do it that way so only the required part would be worked on

Why not replicate the concept in another file and use the dummy for display purposes?

Create a new file with no data and place another image in the same position with the same name.

 

[RoryA]

Try this change to the original code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("L92")) Is Nothing Then Exit Sub 'if any cell other than L92 is selected, the macro is terminated
    If LCase$(Range("L92").Value) = "yes" Then
        Rows("95:96").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
        Shapes("Picture 39").Visible = msoTrue
    Else
        Rows("95:96").EntireRow.Hidden = True 'if anything other than "Yes" is entered in L92 or L92 is blank, the rows are hidden
        Shapes("Picture 39").Visible = msoFalse
    End If
End Sub

 

[PGD15]

Rory are my idol just need to get the picture to format to the cell and this will solve my problem a treat!

 

[RoryA]

just need to get the picture to format to the cell

What does that mean exactly? What formatting?

 

[PGD15]

Don't worry i'm stupid i gave you the wrong image name it was 41 so when just changed it and it works perfectly!

 

[PGD15]

Hi guys 1 further question:

I had three sections i needed to apply this too on the same tab/worksheet I've adapted the code that Rory sent and each part will work individual however as a collective it doesn't work, only the top part will work. i've tried making them all individual subs but then none work. the code is: - ignore the ' bits i didn't bother updating them for each part

Private Sub Worksheet_Change(ByVal Target As Range)
'First range for hidden cells (T&T)
   If Intersect(Target, Range("L49")) Is Nothing Then Exit Sub 'if any cell other than L49 is selected, the macro is terminated
    If LCase$(Range("L49").Value) = "yes" Then
        Rows("52:60").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
        Shapes("Picture 63").Visible = msoTrue
        Shapes("Picture 102").Visible = msoTrue
        Shapes("Picture 109").Visible = msoTrue
    Else
         Rows("52:60").EntireRow.Hidden = True 'if anything other than "Yes" is  entered in L92 or L92 is blank, the rows are hidden
        Shapes("Picture 63").Visible = msoFalse
        Shapes("Picture 102").Visible = msoFalse
        Shapes("Picture 109").Visible = msoFalse
    End If

' Second range for hidden Cells (Linked to RFS)
    If Intersect(Target, Range("L74")) Is Nothing Then Exit Sub 'if any cell other than L74 is selected, the macro is terminated
    If LCase$(Range("L74").Value) = "yes" Then
        Rows("77:82").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
        Shapes("Picture 33").Visible = msoTrue
        Shapes("Picture 35").Visible = msoTrue
    Else
         Rows("77:82").EntireRow.Hidden = True 'if anything other than "Yes" is  entered in L92 or L92 is blank, the rows are hidden
        Shapes("Picture 33").Visible = msoFalse
        Shapes("Picture 35").Visible = msoFalse
    End If
   
'third range to hidden cells (penalities)
    If Intersect(Target, Range("L92")) Is Nothing Then Exit Sub 'if any cell other than L92 is selected, the macro is terminated
    If LCase$(Range("L92").Value) = "yes" Then
        Rows("95:97").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
        Shapes("Picture 41").Visible = msoTrue
    Else
         Rows("95:97").EntireRow.Hidden = True 'if anything other than "Yes" is  entered in L92 or L92 is blank, the rows are hidden
        Shapes("Picture 41").Visible = msoFalse
    End If
End Sub

if there is a line im missing so to make them all work that would help. ... sorry im a bit of a novice only really started using VBA a couple of days ago.

 

[RoryA]

The first If test exits the routine if you didn't change L49. Try this instead:

Private Sub Worksheet_Change(ByVal Target As Range)
'First range for hidden cells (T&T)
   If Not Intersect(Target, Range("L49")) Is Nothing Then
    If LCase$(Range("L49").Value) = "yes" Then
        Rows("52:60").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
        Shapes("Picture 63").Visible = msoTrue
        Shapes("Picture 102").Visible = msoTrue
        Shapes("Picture 109").Visible = msoTrue
    Else
         Rows("52:60").EntireRow.Hidden = True 'if anything other than "Yes" is  entered in L92 or L92 is blank, the rows are hidden
        Shapes("Picture 63").Visible = msoFalse
        Shapes("Picture 102").Visible = msoFalse
        Shapes("Picture 109").Visible = msoFalse
    End If
   End If
' Second range for hidden Cells (Linked to RFS)
    If Not Intersect(Target, Range("L74")) Is Nothing Then
    If LCase$(Range("L74").Value) = "yes" Then
        Rows("77:82").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
        Shapes("Picture 33").Visible = msoTrue
        Shapes("Picture 35").Visible = msoTrue
    Else
         Rows("77:82").EntireRow.Hidden = True 'if anything other than "Yes" is  entered in L92 or L92 is blank, the rows are hidden
        Shapes("Picture 33").Visible = msoFalse
        Shapes("Picture 35").Visible = msoFalse
    End If
   End If
'third range to hidden cells (penalities)
    If Not Intersect(Target, Range("L92")) Is Nothing Then
    If LCase$(Range("L92").Value) = "yes" Then
        Rows("95:97").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
        Shapes("Picture 41").Visible = msoTrue
    Else
         Rows("95:97").EntireRow.Hidden = True 'if anything other than "Yes" is  entered in L92 or L92 is blank, the rows are hidden
        Shapes("Picture 41").Visible = msoFalse
    End If
    End If
End Sub