How to run a hide/unhide row macro based on other cell data validation?

[fimanishi]

Hi,

I am new to the forum and to VBA.

I am creating an Excel sheet for my work, but i am stuck on this. I look over the internet for 2 days, including the forum and couldn't find. The closest i got was this thread : http://www.mrexcel.com/forum/excel-questions/428813-run-macro-after-dropdown-selection-made.html

My code works when i run it in the vba application.

Basically, what i want to do is to unhide/hide a row based on a cell value that will be chosen with data validation.

The code is:

Sub TheSelectCase1()


    Select Case Range("E38").Value


          Case "No"


              Call Unhide_Sub
        
          Case Else
              
              Call Hide_Sub


    End Select


End Sub

Sub Unhide_Sub()


    Rows("38:40").Select
    Selection.EntireRow.Hidden = False


End Sub


Sub Hide_Sub()


    Rows("39").Select
    Selection.EntireRow.Hidden = True


End Sub

What i want to do is to make this macro run all the time. Whenever the cell value is changed, that macro should run. Is that possible to have excel scanning it all the time and calling the macro without any other activation method?

Sorry if this was already answered somewhere, but i couldn't find.

Thanks for the help!

Felipe

 

[AlphaFrog]

Put this code in the woksheet's code module and not a standard code module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "E38" [COLOR=darkblue]Then[/COLOR] Rows("38:40").Hidden = Target.Value = "Yes"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

You may want to use a different row to contain the Yes\No dropdown. You wouldn't be able to unhide the row if the dropdown is also hidden.

 

[Snakehips]

Felipe,

Welcome to Mr Excel.

Try replacing your code with..

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("E38")) Is Nothing Then Exit Sub
If Target = "No" Then
              Rows(39).EntireRow.Hidden = False
 Else
              Rows(39).EntireRow.Hidden = True
End If


End Sub

Hope that helps.

 

[fimanishi]

Put this code in the woksheet's code module and not a standard code module.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Change([COLOR=darkblue]ByVal[/COLOR] Target [COLOR=darkblue]As[/COLOR] Range)
    [COLOR=darkblue]If[/COLOR] Target.Address(0, 0) = "E38" [COLOR=darkblue]Then[/COLOR] Rows("38:40").Hidden = Target.Value = "Yes"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

You may want to use a different row to contain the Yes\No dropdown. You wouldn't be able to unhide the row if the dropdown is also hidden.

Hi! Thanks for the answer.

When you say worksheet's code module you mean the one that is called "ThisWorkbook"?

And should i delete my code and add the one that you posted? Because i added and still it didn't do anything by itself :/

Thanks

 

[AlphaFrog]

The worksheet code module called something like
Sheet1 (Sheet1)

Except it's the specific worksheet that has your E38 dropdown.

Yes; delete your old code.

 

[fimanishi]

Felipe,

Welcome to Mr Excel.

Try replacing your code with..

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("E38")) Is Nothing Then Exit Sub
If Target = "No" Then
              Rows(39).EntireRow.Hidden = False
 Else
              Rows(39).EntireRow.Hidden = True
End If


End Sub

Hope that helps.

Hi Snakehips,

thanks for the answer. I replaced my code with yours

But still nothing happens when i change the value in the dropdown menu, and when i try to run it, the macro window pops-up.

Am i doing something wrong? Should your code be somewhere else?

Thank you!

 

[My Aswer Is This]

You should only have to select Yes from the drop down and press enter. Then the macro runs automatically.

 

[Snakehips]

Felipe,

Right click the sheet tab of the sheet concerned then Click 'View Code'

The code that I gave you or the code that AlphaFrog gave you but NOT both, should be pasted into the white area on the right. i.e. the worksheet's code module.

The code should run automatically if and when the value in cell E38 changes.

 

[fimanishi]

Felipe,

Right click the sheet tab of the sheet concerned then Click 'View Code'

The code that I gave you or the code that AlphaFrog gave you but NOT both, should be pasted into the white area on the right. i.e. the worksheet's code module.

The code should run automatically if and when the value in cell E38 changes.

Snakehips, thank you very much. Your code worked exactly how i wanted :D

I was pasting in the wrong place.

Thank you