Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pop up to flag an entry
Is there any way to have a pop up occur to inform user that to continue a
value must be entered into a certain cell. eg if user selects 'yes' the a pop up flags them to enter a value in adjacent cell |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pop up to flag an entry
Try this:
DataValidationSettingsAllow:Custom, Formula: =A2<"yes" Input message: Uncheck "Show input message when cell is selected Error alert: Check "Show error alert after invalid data is entered, Style: Information, Fill "Title" and "Error message" as you like (e.g. Fill next cell!) OK -- Regards! Stefi €˛Andrew Mogg€¯ ezt Ć*rta: Is there any way to have a pop up occur to inform user that to continue a value must be entered into a certain cell. eg if user selects 'yes' the a pop up flags them to enter a value in adjacent cell |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pop up to flag an entry
You use the word "selects"
Is user selecting from a menu of choices, like a Data Validation Dropdown list or is user simply typing "yes" in a cell? Either way, to get an actual pop up message you would need event code to remind user. This is a reminder only. Private Sub Worksheet_Change(ByVal Target As Range) Const myRange As String = "A1" ' "A1:A10" if a range On Error GoTo endit Application.EnableEvents = False If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then If Target.Value = "yes" Then MsgBox "You must fill in " & Target.Offset(0, 1).Address End If End If endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Edit to suit. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Thu, 3 Dec 2009 17:17:01 -0800, Andrew Mogg wrote: Is there any way to have a pop up occur to inform user that to continue a value must be entered into a certain cell. eg if user selects 'yes' the a pop up flags them to enter a value in adjacent cell |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pop up to flag an entry
Thanks Gord,
However i seem to get a 'compile error' message when i try this. Also if you can have the text that i can change on the example in italics that would be great. (so i know what are commands & what is ultimately displayed. It would seem you have vast amounts of knowledge on this type of programming (if thats what it is) so do you know of a resource for beginners that i may be able to access to help me understand the terminology & process. Thanks again "Gord Dibben" wrote: You use the word "selects" Is user selecting from a menu of choices, like a Data Validation Dropdown list or is user simply typing "yes" in a cell? Either way, to get an actual pop up message you would need event code to remind user. This is a reminder only. Private Sub Worksheet_Change(ByVal Target As Range) Const myRange As String = "A1" ' "A1:A10" if a range On Error GoTo endit Application.EnableEvents = False If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then If Target.Value = "yes" Then MsgBox "You must fill in " & Target.Offset(0, 1).Address End If End If endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Edit to suit. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Thu, 3 Dec 2009 17:17:01 -0800, Andrew Mogg wrote: Is there any way to have a pop up occur to inform user that to continue a value must be entered into a certain cell. eg if user selects 'yes' the a pop up flags them to enter a value in adjacent cell . |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pop up to flag an entry
Did you copy the code exactly as I posted? Maybe you left off the "End Sub"
The error message must given more info than 'compile error'. Did you copy it to the worksheet module? What do you want in Italics? The word you type or choose from a dropdown? Simply format the input cell as Italics. I don't have a vast knowledge of programming. Most of what I learned has been from following the postings on these new groups and shamelessly poaching the code that I need. Some has come from using the macro recorder and using VBA help. A good place to start is David McRitchie's getting started site. http://www.mvps.org/dmcritchie/excel/getstarted.htm A few other sites in no order of preference which can provide examples. http://www.codesites.com/ http://www.contextures.com/ http://www.cpearson.com/excel.htm http://www.j-walk.com/ss/excel/links/ http://www.mvps.org/dmcritchie/excel/excel.htm http://edc.bizhosting.com/english/index.htm http://www.oaltd.co.uk/Excel/Default.htm http://www.vbapro.com/ http://www.rondebruin.nl/code.htm Gord On Mon, 14 Dec 2009 02:06:02 -0800, Andrew Mogg wrote: Thanks Gord, However i seem to get a 'compile error' message when i try this. Also if you can have the text that i can change on the example in italics that would be great. (so i know what are commands & what is ultimately displayed. It would seem you have vast amounts of knowledge on this type of programming (if thats what it is) so do you know of a resource for beginners that i may be able to access to help me understand the terminology & process. Thanks again "Gord Dibben" wrote: You use the word "selects" Is user selecting from a menu of choices, like a Data Validation Dropdown list or is user simply typing "yes" in a cell? Either way, to get an actual pop up message you would need event code to remind user. This is a reminder only. Private Sub Worksheet_Change(ByVal Target As Range) Const myRange As String = "A1" ' "A1:A10" if a range On Error GoTo endit Application.EnableEvents = False If Not Intersect(Target, Me.Range(myRange)) Is Nothing Then If Target.Value = "yes" Then MsgBox "You must fill in " & Target.Offset(0, 1).Address End If End If endit: Application.EnableEvents = True End Sub Right-click on the sheet tab and "View Code". Copy/paste the above into that sheet module. Edit to suit. Alt + q to return to Excel. Gord Dibben MS Excel MVP On Thu, 3 Dec 2009 17:17:01 -0800, Andrew Mogg wrote: Is there any way to have a pop up occur to inform user that to continue a value must be entered into a certain cell. eg if user selects 'yes' the a pop up flags them to enter a value in adjacent cell . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pop up to flag an entry | Excel Discussion (Misc queries) | |||
Flag Help | Excel Discussion (Misc queries) | |||
How do I flag up duplicate data entry in an excel spreadsheet? | Excel Worksheet Functions | |||
Flag 1 in 5 entries | Excel Worksheet Functions | |||
Excel flag | Excel Discussion (Misc queries) |