run macro after choosing in a dropdown list

[Spencinator]

Hello all

I have a dropdown list with 6 options (Cell link H5). If the user of my file chooses option 6, line 23 and 24 of my input sheet should be unhidden. If the user chooses the options 1 to 5, line 23 and 24 should be hidden. How can this be done? I have already a code but it doesn't work!

I made 2 macros which should be running automatically, depending the choose in the dropdownlist:

Sub UnhideNameOfDisease()
Sheets("Input").Activate
If Range("H5").Value = 6 Then
Rows("23:25").Hidden = False
End If
End Sub

Sub HideNameOfDisease()
Sheets("Input").Activate
If Range("H5").Value < 6 Then
Rows("23:25").Hidden = True
End If
End Sub

This macro is to run one of them:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Address <> "$H$5" Then Exit Sub

Select Case Target.Value
Case "1"
Call HideNameOfDisease
Case "2"
Call HideNameOfDisease
Case "3"
Call HideNameOfDisease
Case "4"
Call HideNameOfDisease
Case "5"
Call HideNameOfDisease
Case "6"
Call UnhideNameOfDisease
Case Else
End Select

End Sub

Thanxs for answers!!!

Greets

[antoka05]

Your code works fine.
Please see what is your activecell when you choose an option from dropdown list. If activecell is not H5 your code will not work.

Try also to do this:
Open Visual basic editor (alt + F11)
Go on immediate wirndow (ctrl + g)
type:

Please Login or Register  to view this content.

and press Enter.

I hope it can help you.

Regards,
Antonio

[Spencinator]

Thanxs for your answer, but it still doesn't work... Am i wright, if I have the macro _change in the Sheet5(Input) space and the two other macros in Module1 space?

[antoka05]

If you have macros in Module1 you need to use 'Module1.' as prefix to call the macro, for example:
To call macro 'UnhideNameOfDisease' of Module1 you need to write 'Call Module1.UnhideNameOfDisease' (you can also use '!' instead of '.').

Regards,
Antonio

[Andy Pope]

this works for me.

Please Login or Register  to view this content.

Please use code tags in future.

[Spencinator]

ok, now it works if i enter the numbers myself. But is there a way that it works if I choose in the dropdown list? Because if i don't press the enter button, nothing happens...

[Andy Pope]

I assume the dropdown list is a data validation dropdown and not a control?

[Spencinator]

its the combobox from the "Forms" Toolbar. Is there another list with the same attributes?