message prompts in VBA

VBAmalta89

Board Regular
Joined
Mar 25, 2011
Messages
116
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?
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You can use the ComboBox's Change event procedure. But which cell on which worksheet do you want to check?
 

VBAmalta89

Board Regular
Joined
Mar 25, 2011
Messages
116
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Like this:

Code:
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

Board Regular
Joined
Mar 25, 2011
Messages
116

ADVERTISEMENT

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?
 

VBAmalta89

Board Regular
Joined
Mar 25, 2011
Messages
116

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,991
Messages
5,526,099
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top