Fill in a value for cell with macro, when opening Excel file

[benjamint]

Does anyone know a macro for entering a cell value (date) in cell G1 when opening the Excel file?

Thanks in advance.

 

[benjamint]

Private Sub Workbook_Open()
Dim vDate As Variant
While Not IsDate(vDate)
vDate = InputBox("Whats my Begin Date", "Begin Date Input", Format(Now(), "dd/mm/yyyy"))
If vDate = "" Then '' they presed cancel
Exit Sub 'exit doing nothing
End If
 
If IsDate(vDate) Then
Sheet1.Range("g1").Value = vDate '' a good date has been entered
Exit Sub
Else
MsgBox "No Valid Begin Date entered"
 
End If
Wend
End Sub
[code][/QUOTE]
 
Wow, unbelievable, thank you so much!!! It helps me so much. I really appreciate your help guys!!!

 

[jasonb75]

Private Sub Workbook_Open()
Dim vDate As Variant
While Not IsDate(vDate)
vDate = InputBox("Whats my Begin Date", "Begin Date Input", Format(Now(), "dd/mm/yyyy"))
If vDate = "" Then '' they presed cancel
Exit Sub 'exit doing nothing
End If
 
If IsDate(vDate) Then
Sheet1.Range("g1").Value = vDate '' a good date has been entered
Exit Sub
Else
MsgBox "No Valid Begin Date entered"
 
End If
Wend
End Sub

Use [/code] to close the tags, you're trying to close with the opening tag, that's why it's not formatting correctly.

 

[CharlesChuckieCharles]

I will
thanks

 

[benjamint]

One last question, is there also a possibility to build in an End date? The end date should be put on cell J1. Should this be a separate macro, or is it possible to build 1 macro for both dates, such that it first asks for Begin date and after that asking for the End date?

Once again thanks in advance.

 

[CharlesChuckieCharles]

One last question, is there also a possibility to build in an End date? The end date should be put on cell J1. Should this be a separate macro, or is it possible to build 1 macro for both dates, such that it first asks for Begin date and after that asking for the End date?

Once again thanks in advance.

2 conditions
Suprisingly Now You have to specify what will happen

Start date cancelled, should workvbook ask about an end date?
Is valid start date plus valid end date only

or can it be start date :- a date is entered, end date is cancelled



HOW DO YOU SEE IT WORKING KNOWING HOW IMPERFECT PEOPLE ARE?

 

[benjamint]

2 conditions
Suprisingly Now You have to specify what will happen

Start date cancelled, should workvbook ask about an end date?
Is valid start date plus valid end date only

or can it be start date :- a date is entered, end date is cancelled



HOW DO YOU SEE IT WORKING KNOWING HOW IMPERFECT PEOPLE ARE?

Dear Charles, actually I want both start and end date to be valid, and only if they both are valid, then enter these values.
Is this possible?

 

[jasonb75]

Maybe

Private Sub Workbook_Open()
    Dim dt1 As Long, dt2 As Long, dtcheck As Boolean
line2:
    On Error GoTo line1
        If Not dtcheck Then dt1 = DateValue(InputBox("What is your start date?"))
            dtcheck = 1
            dt2 = DateValue(InputBox("What is your finish date?"))
    If dt2 < dt1 Then
        MsgBox "Must be after Start date, please try again", vbOKOnly
        GoTo line2
    End If
On Error GoTo 0
    Sheet1.Range("G1") = dt1
    Sheet1.Range("J1") = dt2
Exit Sub
line1:
    MsgBox "Not a valid date Please try again", vbOKOnly
Resume line2
End Sub

Conditions

Valid date must be entered as start date.
Valid date must be entered as end date.
No blanks allowed, no cancel allowed, inputbox will stay until a valid date is entered.
End date must not be earlier than start date.

As a getout clause if needed, changing the last 3 lines to

line1:
    If MsgBox("Not a valid date Please try again", vbAbortRetryIgnore) = vbAbort Then ThisWorkbook.Close
Resume line2

would cause the workbook to close unedited if the user chooses to abort without entering a valid date.

 

[CharlesChuckieCharles]

can the dates be the same? can start date be after end date?? all of things a user does

 

[benjamint]

can the dates be the same? can start date be after end date?? all of things a user does

Charles, thanks, this was exactly what I was looking for. The date conditions are also OK, no need to change anything, if the user still succeeds to get the wrong data, then we should doubt the brain of the user!!!!

 

[jasonb75]

Charles, thanks, this was exactly what I was looking for. The date conditions are also OK, no need to change anything, if the user still succeeds to get the wrong data, then we should doubt the brain of the user!!!!

If the end date is before the start date then maybe we should question if the user has a brain to doubt