Hide\Unhide cell based on cell value !!!

[hensleyj]

Hey anyone,

I am having problems trying to work out a macro to hide/unhide a cell based on a value. The value is derived from a IF statement.

Below is the information you may need, any help would be appreciated. I have looked through the forums and used other suggestions for other posts, but none seem to work.

C14= =IF(C10="","",IF(OR(AND(C10="Business Super Directions"),AND(C10="Personal Super Directions")),"Yes","No"))

So, depending on the selection at C10 from a drop down box, C14 will equal the following;

1) BLANK, if C10 option has yet to be selected
2) Yes
3) No

What i am trying to do is the following;

1) BLANK: Show row 15
2) Yes: Show row 15
3) No: Hide row 15

As the value of C14 could change i need the macro to be able to show the above 3 outcomes.
If the cell changes from No to yes, i need it to unhide the cell.

Also, i have seen in some posts, others were having problems with the cell hiding or unhiding unless you hit the enter button.

I am hoping that it is possible for the macro to RUN and hide/unhide purely on the change of value without the need to hit "enter"

Any help would be really appreciated

 

[Pontiyak]

Here's my hide/unhide VBA - I have it assigned to two CommandButtons (one is labeled Hide one Show)


My code runs off "Show" / "Hide", that is the result of an If/Then and is displayed in Column 2

Sub HideRows()
BeginRow = 1
EndRow = 100
ChkCol = 2

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Hide" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

Sub UnHideGridRows()
BeginRow = 1
EndRow = 100
ChkCol = 2

For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "Hide" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
Else
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

 

[Asala42]

You can use a Worksheet Change event if you just want the macro to trigger on a change in a cell.
Right Click on your sheet, hit "View Code", and paste in this code for example (it's not stored in a typical module)

Private Sub Worksheet_Change(ByVal target As Excel.Range)

Dim VRange As Range
Set VRange = Range("C14")

If Union(target, VRange).Address = VRange.Address Then
    Application.EnableEvents = False
        If Range("A14").Value = "No" Then
            Range("A15").EntireRow.Hidden = True
        Else
            Range("A15").EntireRow.Hidden = False
        End If
End If

Application.EnableEvents = True
End Sub

Basically this will hide Row15 if C14 equals "No", otherwise the row will become visible.

 

[hensleyj]

Thanks for your help, but this doesnt seem to work.

I am using excel 2010, just in case that changes anything.

 

[Michael M]

which code doesn't work ??
Did you put the code in the Sheet Module of the worksheet it applies to , Not "This Workbook" as suggested by Asala

 

[hensleyj]

I followed Asala's instructions exactly.

How do i put it in the sheet module exactly?

sorry i am still learning how to do a lot of this.

 

[Michael M]

As Asala mentioned...
Right click on the sheet tab you are using, Select "View Code"
Paste the code provided in the RH window where the cursor is flashing !!

 

[hensleyj]

OHH yeh, sorry i misread your comment. i did exactly that.

 

[Asala42]

This tested well for me.

If you got any errors while editing it the "Application.EnableEvents = False" line might be preventing further attempts. In VBA, try inserting a module and paste/run this code.

Sub Test1()
Application.EnableEvents = True
Msgbox "Events Enabled"
End Sub

Then try changing the value in C14 to "No", if the change event works this should hide row 15.

 

[hensleyj]

i opened up a new blank macro enabled sheet and put in the code with nothing in the sheet, then applied the YES or NO to the relevant cell....no reaction at all

i also put the code you just gave me Asala42, nothing again.

I have tried other codes from other replies to other forums, and it never works.

Is there something wrong with a setting in excel preventing this from happening? it just simply refuses to do it lol