Creating Static Date based on adjacent cell via VBA

RobbieL

Board Regular
Joined
Oct 4, 2007
Messages
109
Good morning,

I have a range (I:I) in which the data is validated to "Yes" or "No" selections.

If the user selects "Yes" in colum "I", I want the date to appear in the adjacent cell in column "J". If the user selects "No", I want column "J" to remain blank. I have managed to get this far however the date is not static, and updates each time the worksheet is opened.

Can anyone assist?

Here is my working so far:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If NotNow = True Then Exit Sub
'if activecell is in Column ? then...
'add date to adjecent column ?+1 cell
If Target.Column = 9 Then
    NotNow = True
    Target.Offset(0, 1) = Now()
    NotNow = False
End If

End Sub
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Are you sure it's updating? If the code read as
Target.Offset(0, 1).Formula = "=Now()"
then I could understand why it would update. Is the problem actually that you don't want the time value to show, in which case you could try:
Target.Offset(0, 1) = Format(Now(), "dd/mm/yyyy")
 
Upvote 0
Are you sure it's updating? If the code read as then I could understand why it would update. Is the problem actually that you don't want the time value to show, in which case you could try:

Thank you both - that seems to have worked changing the "now()" to "date" however I only want the adjacent cell to update if the answer chosen is "Yes".

At the moment it updates regardless of what is chosen?
 
Upvote 0
I did wonder about that, but it didn't seem like that was the bit you were questioning - as if you were aware and would deal with it later... Anyway, this is the sort of thing you want I think:

Private Sub Worksheet_Change(ByVal Target As Range)If NotNow = True Then Exit Sub
'if activecell is in Column ? then...
'add date to adjecent column ?+1 cell
If Target.Column = 9 Then
NotNow = True
If Target.Value = "Yes" Then
Target.Offset(0, 1) = Date
Else
End If
NotNow = False
End If


End Sub
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top