Excel macro and dropdown list value doesn't seem to be passing-CodePudding

I'm sure to people this is going to seem like a simple question but I can't find a way to do it and google is not return examples of the issue I'm having. I'm looking to say that if a cell = value from a dropdown list and another cell value is blank then it should show a message box. What I keep finding is how to call other macros on drop list change.

The code I have is simple and looks like this:

Sub failuremsg()
If H10 = "Failed" And J10 = "" Then
    MsgBox "Failure message is missing", vbOKCancel
End If
End Sub

If I change and test with just J10 in then it brings up the message box. If I test with just H10, which is the dropdown list cell, then it does nothing which indicates to me that there is something I'm missing in the code to tell it that H10 is a dropdown list and if it equals the value I want then to run.

Is anyone able to help or point to an article that could assist with this please as I'm stuck.

Thanks very much for your time.

CodePudding user response：

In this code H10 and J10 are considered to be variables which are empty! They are no cell values.

If you mean them to be cell values you need to fully reference them with workbook, worksheet and range like:

ThisWorkbook.Worksheets("Sheet1").Range("H10").Value

To avoid such issues I recommend always to activate Option Explicit: In the VBA editor go to Tools › Options › Require Variable Declaration. Then you get notified if you use variables that are not declared and you won't end up with something that is enirely different from what you thought it actually is.

Option Explicit

Public Sub failuremsg()
    If ThisWorkbook.Worksheets("Sheet1").Range("H10").Value = "Failed" And ThisWorkbook.Worksheets("Sheet1").Range("J10").Value = vbNullString Then
        MsgBox "Failure message is missing", vbOKOnly 'use OK only if there is no choice for the user anyway!
    End If
End Sub