Formula In Excel To Prompt User To Input Date

pclutts · #1 08-28-2014, 08:11 PM

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.

excelledsoftware · #2 08-30-2014, 09:04 PM

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

charlesdh · #3 08-31-2014, 11:53 AM

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

excelledsoftware · #4 08-31-2014, 12:35 PM

Wow Charles I didnt know about that you could keep the ability to undo commands that way. I learned something. Thanks

charlesdh · #5 08-31-2014, 12:45 PM

Hi,

Glad to help. I hope the user can use it.

Charles

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