message prompts in VBA

[VBAmalta89]

I have a user form and I need to check a value of a cell in a particular worksheet when the user inputs chooses data from a combo box.

If the value of this cell in the worksheet is >0 then a message is prompted otherwise the program can continue normally.

How can i do this in VBA?

 

[Andrew Poulsom]

You can use the ComboBox's Change event procedure. But which cell on which worksheet do you want to check?

 

[VBAmalta89]

the sheet is called calculator and the cell B34

so the code will be under such a sub right?

Private Sub FactoryNum_Change()

and then i create a message box...right?

 

[Andrew Poulsom]

Like this:

Private Sub FactoryNum_Change()
    If Worksheets("calculator").Range("B34").Value <= 0 Then
        MsgBox "B34 must be greater than zero."
        Exit Sub
    End If
'   More code
End Sub

 

[VBAmalta89]

what if the value that needs to be checked in the worksheet varies depending on the option from the dropbox?

is there a way of doing something similar?

 

[Andrew Poulsom]

Yes, but how would it vary?

 

[VBAmalta89]

Consider the following example in a worksheet called components:

Column A Column B
24 90
25 (blank)
26 76
27 89
28 (blank)

Now the combo box drops down the data in column A.

I need the user form to prompt a message if when a value chosen from the drop down box has a corresponding value in column B. after pressing an OK button then the user can continue inputting data in the user form. So it is sort of a warning message only. If the value in column B is blank then no message is prompted.

 

[Andrew Poulsom]

Does your ComboBox have a RowSource?

 

[VBAmalta89]

yes the row source is the data in column A

 

[Andrew Poulsom]

Try:

Private Sub FactoryNum_Change()
    With FactoryNum
        If Range(.RowSource).Cells(.ListIndex + 1, 2).Value <= 0 Then
            MsgBox Range(.RowSource).Cells(.ListIndex + 1, 2).Address(False, False) & " must be greater than zero."
            Exit Sub
        End If
    End With
'   More code
End Sub