#1
|
|||
|
|||
Formula In Excel To Prompt User To Input Date
I have a cell (C16) that has a date format (such as 08/28/2014). I want the value of this cell to be “N/A” when cell C7 is “No”. When C7 is “Yes”, I want Excel to prompt the user to enter a date when they click on cell C16. The formula for cell C7 is =IF(C3="No","No","Yes"). That's where the value of C7="No" comes from. If I didn't need a date for the value of C16, the formula would be something like =IF(C7="No","N/A","______"). My apologies but I need to be led by the hand here. I have never created a macro/module in Excel before. Can anyone out there suggest a solution for me? Thanks in advance for any advice. |
#2
|
|||
|
|||
This is not too hard of a script to write. HOWEVER what you are asking is when the user selects cell C16 to have it run. To my knowledge the only way to do this is with a worksheet change procedure. Meaning the code will run everytime there is a change to the worksheet. Even a selection change will cause it to run. This is not a slow process or anything but it does remove the ability to "Undo" commands. Because of this I dont like writing procedures that remove the undo functionality. If you can live without the undo I will write it for you however if you need the undo functionality then we would need to find a way for the code to run by the user clicking on a box or something like that.
Let me know Thanks |
#3
|
|||
|
|||
Hi,
Here's a bit of code that will fire if cell A16 is changed. Code:
Private Sub Worksheet_Change(ByVal Target As Range) 'Do nothing if more than one cell is changed or content deleted If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Target.Address = "$A$16" Then Application.EnableEvents = False ''''''''''''''''Your code '''''' Application.EnableEvents = True End If End Sub |
#4
|
|||
|
|||
Wow Charles I didnt know about that you could keep the ability to undo commands that way. I learned something. Thanks
|
#5
|
|||
|
|||
Hi,
Glad to help. I hope the user can use it. Charles |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Find and Prompt the user to Replace | Lawmuse | Word VBA | 9 | 02-27-2023 06:36 AM |
User input to a variable on the document | dsm1995gst | Word VBA | 1 | 09-03-2013 03:43 PM |
Replacing text with user input.?.?.? | brad1977 | Word | 3 | 11-20-2012 10:20 AM |
Look up an array based on user input | johnsmb | Excel | 2 | 01-07-2011 01:12 PM |
User Name Prompt Appears When Opening Documents | galleherjazz | Office | 0 | 07-30-2009 08:15 PM |