Message box to change workbook date

[Mldeuser]

I have a workbook that requires the date to be changed on the input tab (which then updates the date fields on the other worksheets). I would like to have a mesage box pop upp when the workbook is opened asking if I want to change the date. If the answer is yese I would like anouther box to pop up letting me enter a date that will populate cell B6 on the input worksheet. If the answer is no then it would leave things as they are.

Any help with this is greatly appreciatated.

Thanks
Mark

 

[Joe4]

You want to add VBA code to the Workbook_Open event, which automatically runs on a VBA enabled workbook when the file is opened. If you are unfamiliar with Event Codes, and where to enter the VBA, have a look here:
http://www.cpearson.com/excel/Events.aspx

The VBA code will look something like this:

Private Sub Workbook_Open()
    Dim MyPrompt1
    Dim MyInput1 As Date
 
    Sheets("Input").Activate
    MyPrompt1 = MsgBox("The current workbook date is " & Range("B6") & vbCrLf & "Would you like to change it?", vbYesNo)
    If MyPrompt1 = vbYes Then
        MyInput1 = InputBox("Enter new workbook date")
        Range("B6") = MyInput1
    End If
 
End Sub

 

[VoG]

Try this: right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Private Sub Workbook_Open()
Dim a As VbMsgBoxResult, b As String
a = MsgBox("Do you want to update the date?", vbYesNo + vbQuestion)
If a = vbNo Then Exit Sub
b = InputBox("Enter the new date")
Sheets("Input").Range("B6").Value = DateValue(b)
End Sub

Save, close and re-open the workbook.

 

[Mldeuser]

It works great

Thanks

Mark