Yes/No message box appears based on cell's value and its selectionproduces a value

[Michael Lanier]

If A1=1, I want a message box to appear and give me the option of
selecting Yes or No to a question it displays. If I select Yes, I
want B1=1. If I select No, I want B1=2. Without a selection being
made, I want B1=0. Can you provide a macro? Thanks.

Michael

 

[Gary''s Student]

This assumes that info is being typed in cell A1:

Private Sub Worksheet_Change(ByVal Target As Range)
Set a = Range("A1")
Set t = Target
Set b = Range("B1")
If Intersect(t, a) Is Nothing Then Exit Sub
If a.Value <> 1 Then Exit Sub
x = Application.InputBox(Prompt:="Yes/No", Type:=2)
Application.EnableEvents = False
If x = "Yes" Then b.Value = 1
If x = "No" Then b.Value = 2
If x = False Then b.Value = 0
Application.EnableEvents = True
End Sub


Because it is worksheet event code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm

 

[Rick Rothstein]

This Change event code should do what you want...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answer As Long
'Application.EnableEvents = False
If Target.Address = "$A$1" Then
If Target.Value = 1 Then
Answer = MsgBox("Select Yes or No.", vbYesNoCancel)
Select Case Answer
Case vbYes
Range("B1").Value = 1
Case vbNo
Range("B1").Value = 2
Case vbCancel
Range("B1").Value = 0
End Select
Else
Range("B1").Value = ""
End If
End If
Application.EnableEvents = True
End Sub

I wasn't entirely sure what you wanted in B1 if the value in A1 is not 1, so
I chose to display nothing ("").

 

[Rick Rothstein]

Sorry, I meant to remove the EnableEvents statements from my code (they were
left over from another test)...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answer As Long
If Target.Address = "$A$1" Then
If Target.Value = 1 Then
Answer = MsgBox("Select Yes or No.", vbYesNoCancel)
Select Case Answer
Case vbYes
Range("B1").Value = 1
Case vbNo
Range("B1").Value = 2
Case vbCancel
Range("B1").Value = 0
End Select
Else
Range("B1").Value = ""
End If
End If
End Sub

 

[Michael Lanier]

Thanks so very much Gary's Student and Rick. I'll be giving your
macros a try when I can return to the computer later today. Your time
and help is much appreciated.

Michael