how to add a date when opening up the worksheet

[dmcgov]

so i have a worksheet "Production Timesheet Checklist" that has a field with a date in it.

i need a macro that adds "7" to a date field ("B1"). Also need the macro to ask if the date should be updated.

i guess i need the worksheet open function to make this work. not sure of what else i need to do.

thoughts everyone?

[Logit]

Try this:

Private Sub Worksheet_Activate()
    Range("B1").Value = 7
    MsgBox "Should the date be updated at this time ? "
End Sub

[Richard Buttrey]

There's no 'worksheet' open event. Did you mean the 'workbook' open event, or perhaps the sheet Activate event?

[Winon]

Hello dmcgov,

Please try this Code:

Option Explicit

Private Sub Worksheet_Activate()
    
    If MsgBox("Should the date be updated at this time ? ", vbCritical + vbYesNo, ("Confirmation Required")) = vbYes Then
    Range("B1").Value = Range("B1").Value + Range("G1").Value
    Else: Range("B1").Value = Range("B1").Value
    Exit Sub
    End If

End Sub

[dmcgov]

thanks logit and richard.

winon, i like your code but i changed the "Range("G1").Value to 7 and now it works the way i was expecting it to. Thanks very much for your code. Gold stars all around.

[dmcgov]

so nevermind, i figured it out. just needed to source the range, like so:

Private Sub Workbook_Open()
    
    If MsgBox("Should the date be updated at this time ? ", vbCritical + vbYesNo, ("Confirmation Required")) = vbYes Then
    Sheets("HWI").Range("B1").Value = Sheets("HWI").Range("B1").Value + 7
    Else: Range("B1").Value = Range("B1").Value
    Exit Sub
    End If

End Sub

[dmcgov]

one question for winon, when i set the workbook_open in the ThisWorkbook, the macro fails on the B1 line. thoughts?

Private Sub Workbook_Open()
    
    If MsgBox("Should the date be updated at this time ? ", vbCritical + vbYesNo, ("Confirmation Required")) = vbYes Then
    Range("B1").Value = Range("B1").Value + 7
    Else: Range("B1").Value = Range("B1").Value
    Exit Sub
    End If

End Sub

the error is "run time error 1004, method range of object _Global failed

[Winon]

Thank you for the continued interest dmcgov,

Very well done, you do me proud with your initiative! WOW!

Instead of the Sheet Name you could also use the Sheet Index No. as shown in the Code below:

Option Explicit

Private Sub Workbook_Open()
 Sheet1.Activate
    If MsgBox("Should the date be updated at this time ? ", vbCritical + vbYesNo, ("Confirmation Required")) = vbYes Then
    Sheet1.Range("B1").Value = Sheet1.Range("B1").Value + Sheet1.Range("G1").Value
    Else: Sheet1.Range("B1").Value = Sheet1.Range("B1").Value
    End If

End Sub

Please note that the Exit Sub Line has been removed, since it is not required. you Can do the Same in the Sheet Code.

You are welcome, glad I could help. Also thank you for the Rep, I appreciate it.!

Kind Regards.

[dmcgov]

Thanks again Winon for all your help. Your the bomb!

[Logit]

Glad you got it worked out. Thanks for the add rep.