Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes/No message box appears based on cell's value and its selectionproduces a value
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes/No message box appears based on cell's value and its selection
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 -- Gary''s Student - gsnu200906 "Michael Lanier" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes/No message box appears based on cell's value and its selection produces a value
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 (MVP - Excel) "Michael Lanier" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes/No message box appears based on cell's value and its selection produces a value
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 -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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 (MVP - Excel) "Michael Lanier" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Yes/No message box appears based on cell's value and itsselection produces a value
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Message Box if Data appears in Column | Excel Programming | |||
message box appears and appears | Excel Discussion (Misc queries) | |||
Removing rows in which 1 cell's value appears on a separate list | Excel Discussion (Misc queries) | |||
Urgent- Help ! (Message 'Calculate' Appears) | Excel Discussion (Misc queries) | |||
Switch To/Retry message appears | Excel Programming |