VBA if cell = yes then input system date within a cell range

[thebigac130]

Hello,

I'm pretty new to vba, I would assume this is an easy fix. (short for I don't have a clue)

Scenario - I have drop downs in my data process, for yes or no.

When the user selects yes I want it to automatically input the system date. however only after columns A,B.

Hope this makes sense.

Excel Kings do your thing.

 

[Joe4]

Welcome to the Board!

I am not quite clear if you are saying the drop-down boxes appear in columns A & B, or if this where you want the system date to be placed?
In which cells do these drop-down boxes appear?
Then where exactly do you want the system date to be placed?
Is this confined to just specific rows?

 

[thebigac130]

Columns A&B need to be clear to type text, but my drop downs are in cells C1 to P1.

In the drop down when you select 'yes' it should input a date within the same cell

 

[Joe4]

but my drop downs are in cells C1 to P1.

From you screen print, it does not look like your "Yes/No" selections start on row 1, but rather on row 3, at the earliest, as it looks like you have two header rows above that. Are you just going to have one row of selections, or will it be starting in row 3, but may have more rows of "Yes/No" selections under that?

In the drop down when you select 'yes' it should input a date within the same cell

If you have a drop-down box that only allows "Yes/No" selections, you will not be able to add a date in the same cell, as that would violate the Data Validation.

 

[thebigac130]

yes starting on row 3 sorry, the drop down isint validated so can be changed.

The idea is to select the dropdown option yes or no. If no then put 'no' but if 'yes input date'.

 

[Joe4]

But if you have a Data Validation drop down box to allow you to choose Yes/No, I don't think it will allow ANY other entries in there.
I think what you want to do might require VBA code where if you select "Yes", it actually removes the Data Validation altogether, and then puts the date in there.
Is that the route you would like to go?

 

[thebigac130]

Yeah that sounds great

 

[Joe4]

OK. We will use a "Worksheet_Change" event procedure to do this. Event Procedures are VBA code that are automatically triggered upon some event happening, like a cell on a worksheet being manually updated.

So, right click on the sheet tab name at the bottom of your screen, and click "View Code". This will open the VB Editor. Paste the following code in your VB Editor window and save:

Private Sub Worksheet_Change(ByVal Target As Range)


    Dim myRange As Range
    
'   Define the range you want to apply this to
    Set myRange = Range("C3:P14")
    
'   See if cell updated is in our pre-defined range
    If Not Intersect(Target, myRange) Is Nothing Then
'       See if they set the value equal to "Yes"
        If Target.Text = "Yes" Then
'       Temporarily disable events, drop data validation, and set value of cell to current date
            Application.EnableEvents = False
            Target.Validation.Delete
            Target = Date
            Application.EnableEvents = True
        End If
    End If
    
End Sub

The only thing you might need to change in the code is this line here:

Set myRange = Range("C3:P14")

Change it to reflect the entire range you want this action to happen to.

Now, anytime "Yes" is selected in any of these cells, it will put in the current date.