User Input Control

[Joe976]

I have moved past the issue of time to decimal, as that was handled quite efficiently here. I am now on to a new "I need help" thing. I would like to have a yes or no question box pop up upon opening the worksheet (or workbook or speadsheet, not quite sure of that terminology yet) which changes the date in a cell depending on the answer. For instance, if the user answers yes, the date in a cell is auto filled minus two days from today's date, if no is answered, then three days would be backed off today's date. I know that =Today()-1 will go one day backwards in date, and assume that if I changed the one to 3, it would do just that. I also am quite sure this will require VBA code. Oh, and the kicker is, I would like this to only pop up on Mondays. Any other day will auto fill the date minus one day. Can This be done in Excel?

 

[baitmaster]

Yes this can be done, and yes you're right that you need to use VBA to ask questions on demand

Workbook = the whole Excel file, which consists of one or more Sheets which can be either Worksheets or Chart sheets

I assume you want the code to run when you open the file, so the following code needs to be added to the ThisWorkbook code module in the VB Editor. Note you'll need to create a named range in your workbook, and update the code accordingly

Option Explicit

Private Sub Workbook_Open()


Dim daysBefore As Integer


' only ask question on Mondays
If Weekday(Date) = 2 Then
    
    ' ask your question
    Dim answer As Integer: answer = MsgBox("Question...", vbYesNo + vbQuestion, "Answer the question...")
    
    ' decide what to do
    If answer = vbYes Then
        daysBefore = 2
    Else
        daysBefore = 3
    End If


Else
    
    ' all other days = 1
    daysBefore = 1


End If


' update named range
Range("Your named range here").Value = Date - daysBefore


End Sub

 

[Joe976]

Works great! Of course, I had to figure out named ranges, then I wanted this to work on several different non-adjacent cells, so it took me a while to figure that out. I learned a lot on this little project, and will soon think of some other way for Excel to challenge me. Thanks for the help!