Excel VBA - Data Validation List Automtic Macro

[Gian624]

Good Day,


I am helping acolleague of mine with some VBA code I am rather inexperienced with. She has aData Validation drop down list to select from. When a user makes a selectionfrom drop down, it hides and unhides specific rows based on what the colleaguewants the user to see. The code works as expected but the problem lies withblanks. If a user pushes the delete key when they click on the cell with the drop-downbox, the code goes into debug mode. I’ve tried a great any ways to end the subif that happens but all unsuccessfully. Please let me know if you have anythoughts. I can’t figure out how to get another if statement inside the Casestatement (that was my thought).


Thanks so much in advance,
Matt


Option Explicit

Private Sub Drop_Down(ByVal Target As Range)

ActiveSheet.Activate
If Not Application.Intersect(Range("D44"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value

Case Is = "": Rows("47:92").EntireRow.Hidden = False

Case Is = "Condition One": Rows("67:92").EntireRow.Hidden = True
Rows("47:67").EntireRow.Hidden = False

Case Is = "Condition Two": Rows("67:92").EntireRow.Hidden = True
Rows("47:67").EntireRow.Hidden = False

Case Is = "Condition Three": Rows("67:92").EntireRow.Hidden = True
Rows("47:67").EntireRow.Hidden = False

End Select

End If


End Sub

 

[Fluff]

What error number & message do you get? and if you click Debug, what line of code does it take you to?

 

[Gian624]

Hello Fluff,

The error number is 13 and it takes you to the very first "Case" line n debug mode.

 

[Fluff]

And the error message?

Also what type of drop-down is being used, because that is not a standard worksheet event code?

 

[Gian624]

Error Code 13 is “Type mismatch.” Itold my colleague the same thing that I was unfamiliar with the code or format.She said she found in on google.

 

[Fluff]

I suspect that she is using an ActiveX combobox, rather than Data validation.

The error suggest that cell D44 contains an error such as #N/A or #VALUE!

 

[Gian624]

No, it is a Data Validation box. It only debugs when you push the delete key. Everything else about it works. I just want to find a way to not go to debug mode if someone pushes delete when on D44.

 

[Fluff]

There is nothing wrong with the code.
If it is a data validation box, then there is some other code involved, as that code will not run automatically when the DV is changed.

 

[Gian624]

I assure you there is no other code. I too didn't believe it would work until I watched it work.

 

[Fluff]

Can you supply a sanitised copy of the workbook?