Prompt messagebox depending on cell value

[CV899000]

Hi,

I need to have a messagebox prompt depending on a cell value.

If cell A20 is NOT "Yes" and the user chooses "Yes" in cell A22, then a message box should prompt saying "This option is only possible by enabling A20" and then if the user presses "Ok" in the message box, then cell A20 should change to "Yes" and cell B20 should change to "1".

If the user chooses "No" in the message box, the A22 cell shall be cleared (empty).



If cell A20 contains "Yes" before choosing anything in A22, then the message box shall not appear.

 

[Johnny C]

Try this:-

If Cells(22,1)="" then Exit Sub      
If Cells(20, 1).Value <> "Yes" And Cells(22, 1).Value = "Yes" Then
    FixIt = MsgBox("This option is only possible by enabling A20" & vbCrLf & vbCrLf & "Fix it?", vbYesNo)
    If FixIt = vbYes Then 
        Cells(20, 1).Value = "Yes"
        Cells(20, 2).Value = 1
    Else
        Cells(22,1).value = ""
End If

You can tell the msgbox to be a Yes/No type rather than a standard OK, and it will return either vbYes or vbNo values depending on what was clicked.

You need the worksheet_Change sub name as per CV's post below. You need the first line because if the code changes the value in A22 then the code will run again, if you didn't have that it might go into an endless loop.

 

[CV899000]

I have this code for now, but this does not work perfect.

[Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A22").Address Then
If Target.Value = "Yes" Then
If MsgBox("A20 is required for this option. Enable?", vbYesNo) = vbNo Then
Worksheets("Choose sheet").Range("A22").Value = ""
Exit Sub
End If
Worksheets("Choose sheet").Range("A20").Value = "Yes"
Worksheets("Choose sheet").Range("B20").Value = "1"
Exit Sub
End If
End If
End Sub/CODE]

If cell A20 is "yes", and the user chooses "yes" in cell A22, then the message box still prompts. I need something that checks if cell A20 is "Yes" and if so, then stops the sub before it prompts the msgbox.

 

[CV899000]

I do not get any messagebox with this code?

 

[Johnny C]

This works for me. I missed out an End I I think.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> Range("A22").Address Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Offset(-2, 0).Value <> "Yes" And Target.Value = "Yes" Then
    FixIt = MsgBox("This option is only possible by enabling A20" & vbCrLf & vbCrLf & "Fix it?", vbYesNo)
    If FixIt = vbYes Then
        Target.Offset(-2, 0).Value = "Yes"
        Target.Offset(0, 1).Value = 1
    Else
        Target.Value = ""
    End If
End If

End Sub