VBA to enter a date in A2

[djl0525]

Hi,

I have a number in Sheet1!A1. If A2 is empty, I want VBA to enter the current date plus the number in cell A1 and save the file.

If A2 is not empty, display "Date entered." in a message box.

Thanks in advance for your consideration.
DJ

 

[Momentman]

Something like this,

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
        If Target.Value = "" Then
            Target.Offset(-1, 0).Value = Date + Target.Offset(-1, 0).Value
        Else
            MsgBox "Date Entered"
        End If
    End If
End Sub

Right click Sheet1, select View Code and paste the above code in there

 

[djl0525]

Thanks, Momentman! The code is great and much appreciated.

Can you help me incorporate it into an existing macro? And can you use the cell address instead of offset so I can easily change it, please?

Sub ShowExerciseSheets()'This macro is run from the Welcome sheet
'It will unhide the Intro sheet, 6 exercise sheets and the blank sheet
'Comment out the sheets do not want to unhide


Dim x As Date
    
Application.ScreenUpdating = False


'Edit expiration date
x = "4/21/2014"


'Expiration message to users


MsgBox _
"You have " & x - Date & " days left to run this exercise."


'"This Excel exercise is provided courtesy of DJL Training, Inc." & vbCrLf & _
'"This file expires on " & x & " & vbCrLf & vbCrLf & _


'If exp date is in the future, unhide the sheets
If x > Date Then


MsgBox ThisWorkbook.Sheets("MsgBoxes").Range("B2").Value
Sheet05.Visible = True  'unhide Introduction
Sheet10.Visible = True  'unhide 1st exercise sheet
Sheet20.Visible = True  'unhide 2nd exercise sheet
Sheet30.Visible = True  'unhide 3rd exercise sheet
Sheet40.Visible = True  'unhide 4th exercise sheet
Sheet50.Visible = True  'unhide 5th exercise sheet
'Sheet60.Visible = True  'unhide 6th exercise sheet
Sheet99.Visible = True  'unhide blank sheet
Application.ScreenUpdating = True


    End If
    
Application.ScreenUpdating = True
    
End Sub

 

[djl0525]

Can anybody help me with this code?

DJ