Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-28-2014, 08:11 PM
pclutts pclutts is offline Formula In Excel To Prompt User To Input Date Windows 8 Formula In Excel To Prompt User To Input Date Office 2013
Novice
Formula In Excel To Prompt User To Input Date
 
Join Date: Aug 2014
Posts: 1
pclutts is on a distinguished road
Default 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.
Reply With Quote
  #2  
Old 08-30-2014, 09:04 PM
excelledsoftware excelledsoftware is offline Formula In Excel To Prompt User To Input Date Windows 7 64bit Formula In Excel To Prompt User To Input Date Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

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
Reply With Quote
  #3  
Old 08-31-2014, 11:53 AM
charlesdh charlesdh is offline Formula In Excel To Prompt User To Input Date Windows 7 32bit Formula In Excel To Prompt User To Input Date Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 08-31-2014, 12:35 PM
excelledsoftware excelledsoftware is offline Formula In Excel To Prompt User To Input Date Windows 7 64bit Formula In Excel To Prompt User To Input Date Office 2003
IT Specialist
 
Join Date: Jan 2012
Location: Utah
Posts: 455
excelledsoftware will become famous soon enough
Default

Wow Charles I didnt know about that you could keep the ability to undo commands that way. I learned something. Thanks
Reply With Quote
  #5  
Old 08-31-2014, 12:45 PM
charlesdh charlesdh is offline Formula In Excel To Prompt User To Input Date Windows 7 32bit Formula In Excel To Prompt User To Input Date Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

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

Charles
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula In Excel To Prompt User To Input Date Find and Prompt the user to Replace Lawmuse Word VBA 9 02-27-2023 06:36 AM
Formula In Excel To Prompt User To Input Date User input to a variable on the document dsm1995gst Word VBA 1 09-03-2013 03:43 PM
Formula In Excel To Prompt User To Input Date 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

Other Forums: Access Forums

All times are GMT -7. The time now is 06:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft