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!

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in L92 and exit the cell.

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 Target = "Yes" Then
        Rows("95:96").EntireRow.Hidden = False 'if "Yes" is entered in L92, rows are visible
    Else
        Rows("95:96").EntireRow.Hidden = True 'if anything other than "Yes" is entered in L92 or L92 is blank, the rows are hidden
    End If
End Sub

 

[Rhodie72]

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!

Very possible. If you know any VBA code, which seems doubtful, then it will b easier for you; I'll try something for you...

 

[PGD15]

Hi,

This works, however do you also know how to reverse it so when cell L92 displays any value other then yes then cells L95,96 become unhidden too?
So if the L92 says yes then 95,96 are hidden if not they become unhidden then this will work perfectly and you're a life saver!

 

[Rhodie72]

ADVERTISEMENT

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!

Private Function PGD15(myRange) As Boolean
'   If a certain cell equals a certain value then hide a row _
    otherwise this row should be displayed.
    
    Select Case LCase(myRange.Value)
    '   E.g. If cell value L92 is populated with the value Yes,
    Case "yes"
    '   then I want rows 95 & 96 to be on show.
        PGD15 = True
    Case Else
    '   If L92 is blank/empty or filled with any value other than yes _
        (<> yes) then i want rows 95 & 66 to be hidden.
        PGD15 = False
    End Select
'   (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!
End Function
Sub SelectRange()
    Dim myRange As Range

    Set myRange = Application.InputBox("Which cell do you want to check?" & vbCrLf & _
    "You may select it directly or use the default value.", "Hide rows", "$L$95", Type:=8)

    Select Case PGD15(myRange)
    '   E.g. If cell value L92 is populated with the value Yes,
    Case True
    '   then I want rows 95 & 96 to be on show.
            Rows("95:95").EntireRow.Hidden = False
            Rows("96:96").EntireRow.Hidden = False
    Case False
    '   If L92 is blank/empty or filled with any value other than yes _
        (<> yes) then i want rows 95 & 66 to be hidden.
            Rows("95:95").EntireRow.Hidden = True
            Rows("66:66").EntireRow.Hidden = True
    End Select

    myRange = ""
End Sub

 

[PGD15]

Is this is also possible to hide an image? so if cell L92 says yes display the image if cell 92 doesn't say yes or is blank hide the image forget i had a picture linked placed next to the cells

 

[Rhodie72]

ADVERTISEMENT

I'm afraid that's beyond my knowledge.

 

[RoryA]

Do you know the name of the picture? (Select it and see what it says in the name box on the left of the formula bar)

 

[mumps]

It is always easier to help and test possible solutions if we could work with your actual file. Perhaps you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells and worksheets. If the workbook contains confidential information, you could replace it with generic data.

 

[Rhodie72]

Is this is also possible to hide an image? so if cell L92 says yes display the image if cell 92 doesn't say yes or is blank hide the image forget i had a picture linked placed next to the cells

The only thing I can suggest is that you format the image to resize with the cells so that when the rows are hidden, that picture will resize to the same height of those rows. The alternative is that you set up a new portion of code that imports the image or removes the image according to the state of visisbilty of those rows. If you go down that route, then you may be better off creating an application that displays the image in a form instead.