CODEWITHSUNDEEP
vbaexcel
cam
cam

Reputation: 57

Excel Macro YesNo message box, different directions for Yes and No

The user has two options in the YesNo Message Box. If No, it performs a certain sequence of filters, but I want to filter another column if the user answers Yes to the message box question. Currently, at "Else", I get an error that reads "Compile error: Function call on left-hand side of assignment must return Variant or Object" If I take out "Else", and the code after it, the macro runs smoothly, but only filters when the user selects No.

If MsgBox("Is This Item Catch Weight?", vbYesNo) = vbNo Then
    retval = InputBox("Please Enter PO Cost")
    ActiveSheet.Range("$A$1:$CL$293662").AutoFilter Field:=71,         Criteria1:="=" & retval
    retval = InputBox("Please Enter Net Weight")
    ActiveSheet.Range("$A$1:$CL$293662").AutoFilter Field:=41, Criteria1:="=" & retval
Else: MsgBox("Is This Item Catch Weight?", vbYesNo) = vbYes
    retval = InputBox("Please Enter PO Cost")
    ActiveSheet.Range("$A$1:$CL$293662").AutoFilter Field:=71, Criteria1:="=" & retval
End If
End If

Upvotes: 1

Views: 3097

Answers (1)

Brad
Brad

Reputation: 12255

A few things are happening here. : new line character in VBA code so a line like 

Else: MsgBox("Is This Item Catch Weight?", vbYesNo) = vbYes

is actually the same as

Else
   MsgBox("Is This Item Catch Weight?", vbYesNo) = vbYes

Which is not what you want.

There is also an extra End If at the end. Remove that.

Calling the message box to appear multiple times is probably not what oyu want either. Likely you want to show the message box, get the result then do something with it.

Dim response As VbMsgBoxResult
response = MsgBox("Is This Item Catch Weight?", vbYesNo)
If response = vbNo Then
    'do stuff for yes
ElseIf response = vbYes Then
    ' do stuff for No
End If

I'd also suggest not using ActiveSheet unless you are sure that's what you want.

Upvotes: 3

Related Questions