CODEWITHSUNDEEP
excelvbadrop-down-menu
user1663003
user1663003

Reputation: 155

Pass cell value that is based on dropdown list

I'm looking to say that if a cell = value from a dropdown list and another cell value is blank then show a message box.

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 then it brings up the message box.

If I test with just H10, which is the dropdown list cell, then it does nothing.

Upvotes: 1

Views: 638

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57683

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

Upvotes: 1

Related Questions