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
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

L33

Board Regular
Joined
Jul 2, 2008
Messages
104
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")
 

RobbieL

Board Regular
Joined
Oct 4, 2007
Messages
109

ADVERTISEMENT

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?
 

L33

Board Regular
Joined
Jul 2, 2008
Messages
104
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
 

RobbieL

Board Regular
Joined
Oct 4, 2007
Messages
109
Apologies - was written in haste!

Brilliant, thanks for your help.

Rob
 

Watch MrExcel Video

Forum statistics

Threads
1,123,057
Messages
5,599,545
Members
414,316
Latest member
ExcelLee

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
Top