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.

 

[jasonb75]

You don't need a macro for that,

enter =TODAY() in G1

 

[CharlesChuckieCharles]

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

Thanks in advance.

in the Thisworkbook module

Private Sub Workbook_Open()
 
sheet1.range("g1").value=format(now(),"dd/mm/yyyy")
 
 
End Sub
[CODE]
 
Sheet1 can be replaced by what ever works for you

 

[jasonb75]

in the Thisworkbook module

Private Sub Workbook_Open()
 
sheet1.range("g1").value=format(now(),"dd/mm/yyyy")
 
 
End Sub

Sheet1 can be replaced by what ever works for you

Why not just use

Private Sub Workbook_Open()
Range("G1") = Date
End Sub

although it seems like a pointless event when a simple worksheet function can perform the same task more effectively.

 

[benjamint]

Why not just use

Private Sub Workbook_Open()
Range("G1") = Date
End Sub

although it seems like a pointless event when a simple worksheet function can perform the same task more effectively.

What I am looking for the following:

After I open my Excel file, I want that a message box pops up, and asks me: What is your Begin Date?
And after I enter my Begin date, like 04/01/2011 it should be put in cell G1.

Anyone any idea how this could be done with a macro?

 

[CharlesChuckieCharles]

What I am looking for the following:

After I open my Excel file, I want that a message box pops up, and asks me: What is your Begin Date?
And after I enter my Begin date, like 04/01/2011 it should be put in cell G1.

Anyone any idea how this could be done with a macro?

Private Sub Workbook_Open()
Dim vDate As Variant
vDate = InputBox("Whats my Begin Date", "Begin Date Input", Format(Now(), "dd/mm/yyyy"))

If IsDate(vDate) Then
    Sheet1.Range("g1").Value = vDate
Else
    MsgBox "No Begin date entered"
'   Sheet1.Range("g1").Value =""
End If

End Sub
[Code]
 
you might want to think about what happens if they don't enter a date
i've commented out the cell reset

 

[jasonb75]

If you ask the right question then you get the right answer.

Private Sub Workbook_Open()
sheet1.range("G3") = datevalue(inputbox("What is your start date?")) 
End Sub

note this must be in the "ThisWorkbook" module.

 

[benjamint]

Great!!!! This is what I was looking for, but one question more:

Is it also possible to let the user go back and enter the correct Begin Date when entered in wrong format. Like instead of entering 04/01/2011 they just entered "TEST".

I mean is it possible to build it in in this macro:

Private Sub Workbook_Open()
Dim vDate As Variant
vDate = InputBox("Whats my Begin Date", "Begin Date Input", Format(Now(), "dd/mm/yyyy"))

If IsDate(vDate) Then
Sheet1.Range("g1").Value = vDate
Else
MsgBox "No Begin date entered"
' Sheet1.Range("g1").Value =""
End If

End Sub
[Code]

 

[jasonb75]

Force a loop with an error test

Private Sub Workbook_Open()
line2:
On Error GoTo line1
Sheet1.Range("G3") = DateValue(InputBox("What is your start date?"))
On Error GoTo 0
Exit Sub
line1:
MsgBox "Not a valid date Please try again", vbOKOnly
Resume line2
End Sub

 

[CharlesChuckieCharles]

Great!!!! This is what I was looking for, but one question more:

Is it also possible to let the user go back and enter the correct Begin Date when entered in wrong format. Like instead of entering 04/01/2011 they just entered "TEST".

I mean is it possible to build it in in this macro:

Private Sub Workbook_Open()
Dim vDate As Variant
vDate = InputBox("Whats my Begin Date", "Begin Date Input", Format(Now(), "dd/mm/yyyy"))
 
If IsDate(vDate) Then
Sheet1.Range("g1").Value = vDate
Else
MsgBox "No Begin date entered"
' Sheet1.Range("g1").Value =""
End If
 
End Sub
[Code][/QUOTE]
 
 
[code]
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]