CODEWITHSUNDEEP
vbaif-statementmacrosconditional-statementsmessagebox
excelnewbie
excelnewbie

Reputation: 1

Message box based on two Conditions

I need your help again. I'm trying to create a Message Box based on two Conditions. I have two lists: One with numbers from 1 to 20. And the second one with: Single Stock, Single Option, Basket of Stocks and Basket of Options.

When choosing Single Stock/Single Option in Sheet "Input" cell "F7", it should be only possible to use the number "1" in cell "F8" otherwise the Message box with error should be displayed.

When choosing Basket of Stocks/Options in Sheet "Input" cell "F7", it should be only possible to use a number >1 in cell "F8" otherwise the Message box with error should be displayed.

I've tried to use this code:

Sub Msg_exe()
If Target.Address = "$F$8" Then
  If Target.Value > 2 Then
    If Target.Address = "Stock" Then
      If Target.Address = "Option" Then
        MsgBox "Error!", vbExclamation, "Error"
      End If
    End If
  End If
End If

Sub Msg_exe()
If Target.Address = "$F$8" Then
  If Target.Value < 2 Then
    If Target.Address = "Basket of Stocks" Then
      If Target.Address = "Basket of Options" Then
        MsgBox "Error!", vbExclamation, "Error"
      End If
    End If
  End If
End If

Upvotes: 0

Views: 1363

Answers (2)

user3598756
user3598756

Reputation: 29421

You can start from this code:

If Target.Address = "$F$8" Then
    Select Case Range("F7")
        Case "Single Stock", "Single Option"
            If Range("F8").Value <> 1 Then MsgBox "Error!", vbExclamation, "Error"
        Case "Basket of Stocks", "Basket of Options"
            If Range("F8").Value <= 1 Then MsgBox "Error!", vbExclamation, "Error"
    End Select
End If

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19712

Rather than use VBA you could just add data validation to cell F8:
=IF(OR($F$7="Stock",$F$7="Option"),$F$8=1,IF(OR($F$7="Basket of Stocks",$F$7="Basket of Options"),$F$8>1,""))

Upvotes: 2

Related Questions