VBA Macro - hides rows based on a single cell which is limited by data validation

[macca_18380]

Hi,

I'm new to this forum, forgive me if i don't understand the rules of engagement.

I'm still very much an apprentice when it comes to VBA, i did manage to find a thread on another site which addressed my need in part but the answer wouldn't work.

Essentially, I would like a macro to hide rows of data dependent upon text, which is selected via data validation, in a single cell.

Below is the code that i found and have edited to suit my needs, albeit it doesn't actually work.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCell As Range: Set iCell = Intersect(Range("F5"), Target)

If iCell Is Nothing Then Exit Sub

If iCell.Value = "PPA Rate" Then

Rows("8:12").Hidden = False

Rows("13:17").Hidden = True

ElseIf iCell.Value = "Dev Fee" Then

Rows("8:12").Hidden = True

Rows("13:17").Hidden = False

'Else ' do nothing

End If

End Sub

The macro doesn't work when i paste this into a module, why is this the case?

Thank you
Michael

 

[My Aswer Is This]

You said:
The macro doesn't work when i paste this into a module, why is this the case?

This script is a sheet change script.
The script runs when you change a value in the sheet:

Right click on the sheet Tab and select view code and past the script in there.

Here use this as a test:
A Message box will popup if you enter the correct text.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iCell As Range: Set iCell = Intersect(Range("F5"), Target)

If iCell Is Nothing Then Exit Sub

If iCell.Value = "PPA Rate" Then
MsgBox "PPA Rate"
Rows("8:12").Hidden = False

Rows("13:17").Hidden = True

ElseIf iCell.Value = "Dev Fee" Then
MsgBox "Dev Fee"
Rows("8:12").Hidden = True

Rows("13:17").Hidden = False

'Else ' do nothing

End If

End Sub

 

[macca_18380]

That works brilliantly, thank you. Is it possible to get the same effect but without a MsgBox popping up?

 

[My Aswer Is This]

That works brilliantly, thank you. Is it possible to get the same effect but without a MsgBox popping up?

Yes. Just delete those two lines of code

 

[My Aswer Is This]

Actually, I like using select case.

Look at this code and try it if you want.
Eliminates using a lot of if statements.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$F$5" Then

Select Case Target.Value
    Case "PPA Rate"
        Rows("8:12").Hidden = False
        Rows("13:17").Hidden = True

    Case "Dev Fee"
        Rows("8:12").Hidden = True
        Rows("13:17").Hidden = False
End Select

End If

End Sub

 

[macca_18380]

This works wonderfully, thank you so much

 

[My Aswer Is This]

This works wonderfully, thank you so much

Glad I was able to help you.
Have a nice day