Date formula that wont change the values each day.

[seanturner9005]

Im sure there is a simple answer to this, but cant get to it. I have an excel workbook that keeps records on a daily basis. One of the fields I fill in is the date. I understand that i can have the formula =TODAY() that will automatically imput the date that is on my computer system which is what i want, but when i open it up the next day, because the formula is still there, the records that i put in yesterday will have today's date in.

Is there anyway so that when i go to save it, it saves the value in the cell. Think i need a different formula.
Hope I have explained it OK.

Many Thanks

seanturner9005

[Kevin UK]

Hi seanturner9005

How about using on the keyboard.

CTRL + ;

[alansidman]

Use this VBA

Please Login or Register  to view this content.

You can attach this to a command button.

[seanturner9005]

Kevin, Thanks for the reply. Handy little shortcut to know, but am trying to tie this into a userform if possible.Alan also many thanks for the reply, think this is what i need. In VBA would you possibly be able to tell me where i enter this so it automatically fills one of the text boxes on my userform. Im new with VBA as of last week so still trying to get my head around it.

'check for a part number
If Trim(Me.txtdat.Value) = "" Then
Me.txtdat.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtdat
ws.Cells(iRow, 3).Value = Me.txtComCode.Value
ws.Cells(iRow, 6).Value = Me.txtItem.Value
ws.Cells(iRow, 8).Value = Me.ComboBox1.Value
ws.Cells(iRow, 9).Value = Me.txtNote.Value
ws.Cells(iRow, 10).Value = Me.txtgreentree.Value


'clear the data
Me.txtdat.Value = ""
Me.txtComCode.Value = ""
Me.txtItem.Value = ""
Me.ComboBox1.Value = ""
Me.txtNote.Value = ""
Me.txtgreentree.Value = ""
Me.txtdat.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub txtDate_Change()

End Sub

Private Sub txtPart_Change()

End Sub

Private Sub txtQty_Change()

End Sub

Private Sub txtdat_Change()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub





HOPE THIS MAKES SENSE, SORRY IF IM JUST TALKING CR#P

[seanturner9005]

Kevin, Thanks for the reply. Handy little shortcut to know, but am trying to tie this into a userform if possible.Alan also many thanks for the reply, think this is what i need. In VBA would you possibly be able to tell me where i enter this so it automatically fills one of the text boxes on my userform. Im new with VBA as of last week so still trying to get my head around it.

'check for a part number
If Trim(Me.txtdat.Value) = "" Then
Me.txtdat.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtdat
ws.Cells(iRow, 3).Value = Me.txtComCode.Value
ws.Cells(iRow, 6).Value = Me.txtItem.Value
ws.Cells(iRow, 8).Value = Me.ComboBox1.Value
ws.Cells(iRow, 9).Value = Me.txtNote.Value
ws.Cells(iRow, 10).Value = Me.txtgreentree.Value


'clear the data
Me.txtdat.Value = ""
Me.txtComCode.Value = ""
Me.txtItem.Value = ""
Me.ComboBox1.Value = ""
Me.txtNote.Value = ""
Me.txtgreentree.Value = ""
Me.txtdat.SetFocus

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub TextBox1_Change()

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub txtDate_Change()

End Sub

Private Sub txtPart_Change()

End Sub

Private Sub txtQty_Change()

End Sub

Private Sub txtdat_Change()

End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Please use the button!"
End If
End Sub





HOPE THIS MAKES SENSE, SORRY IF IM JUST TALKING CR#P

[alansidman]

I would add it as follows at the end of the list of data you are transferring to the spreadsheet from the form.

ie.

Please Login or Register  to view this content.

where x is the column number where you want to place the date.