PDA

View Full Version : Solved: Auto Date Populate in form



Emoncada
04-09-2007, 07:56 AM
Hi I would like to know how can i can have the date and Time auto populate when a text box has a value.

Example
TxtName when a value is entered then LblDate = Date

I would like for it to give me the date and time ("mm:dd:yyyy"; "hh:mm:ss")

How can I make this happen.

mdmackillop
04-09-2007, 07:57 AM
Is this on a Userform?

Emoncada
04-09-2007, 07:58 AM
yes

mdmackillop
04-09-2007, 08:04 AM
Private Sub TxtName_AfterUpdate()
lblDate.Caption = Format(Now, "mm:dd:yyyy") & "; " & Format(Now, "hh:mm:ss")
End Sub

lucas
04-09-2007, 08:05 AM
Private Sub TextBox1_Change()
Label1.Caption = Format(Now, "mmmm dd, yyyy")
Label2.Caption = Format(Now, "h:mm:ss AM/PM")
End Sub

Emoncada
04-09-2007, 08:12 AM
That works only thing is I have a clear button that when clickes update the date instead of clears it. It will clear after clciked twice, Can that be fixed?

mdmackillop
04-09-2007, 08:32 AM
Can you post your code?

Emoncada
04-09-2007, 08:45 AM
This is what I got
Private Sub CommandButton1_Click()
TxtFinalStatus = "Complete"
End Sub

Private Sub CommandButton2_Click()

'Add New Button to your UserForm so you press to Apply Data to Sheet
Dim RowNext As Integer

'last row of data puls one row
RowNext = Worksheets("Server Build Template").Cells(65536, 1).End(xlUp).Row + 1

'Cells(Row Number,Column Number)
With Worksheets("Server Build Template")
.Cells(RowNext, 1) = TxtName.Value
.Cells(RowNext, 2) = LblDate.Caption
.Cells(RowNext, 3) = TxtServerName.Value
.Cells(RowNext, 4) = TxtLocation.Value

If Me.ButtonG4 = True Then .Cells(RowNext, 5) = Me.LblML370G4.Caption
If Me.ButtonG5 = True Then .Cells(RowNext, 5) = Me.LblML370G5.Caption

.Cells(RowNext, 6) = TxtCtsContact.Value
.Cells(RowNext, 7) = TxtTracking.Value

If Me.Button72 = True Then .Cells(RowNext, 8) = Me.Lbl728GB.Caption
If Me.Button146 = True Then .Cells(RowNext, 8) = Me.Lbl146GB.Caption

.Cells(RowNext, 9) = TxtDrive1SN.Value
.Cells(RowNext, 10) = TxtDrive2SN.Value
.Cells(RowNext, 11) = TxtDrive3SN.Value
.Cells(RowNext, 12) = TxtDrive4SN.Value
.Cells(RowNext, 13) = TxtFinalStatus.Value
.Cells(RowNext, 14) = TxtSignature.Value
End With
ServerBuild.PrintForm

'You can't save a worksheet
ActiveWorkbook.Save

End Sub

Private Sub CommandButton3_Click()
ServerBuild.Hide
End Sub

Private Sub CommandButton4_Click()
TxtName.Value = ""
LblDate.Caption = ""
TxtServerName.Value = ""
TxtLocation.Value = ""
TxtCtsContact.Value = ""
TxtTracking.Value = ""
Me.ButtonG4 = False
Me.ButtonG5 = False
Me.Button146 = False
Me.Button72 = False
TxtDrive1SN.Value = ""
TxtDrive2SN.Value = ""
TxtDrive3SN.Value = ""
TxtDrive4SN.Value = ""
TxtFinalStatus.Value = ""
TxtSignature.Value = ""
TxtName.SetFocus

End Sub

Private Sub CommandButton5_Click()
TxtSignature = TxtName.Value
End Sub

Private Sub TxtName_AfterUpdate()
LblDate.Caption = Format(Now, "mm/dd/yyyy") & " " & Format(Now, "hh:mm:ss AM/PM")
End Sub

mdmackillop
04-09-2007, 08:50 AM
Private Sub Txtname_AfterUpdate()
If txtName = "" Then
lblDate.Caption = ""
Else
lblDate.Caption = Format(Now, "mm:dd:yyyy") & "; " & Format(Now, "hh:mm:ss")
End If
End Sub

Bob Phillips
04-09-2007, 08:57 AM
Private Sub TxtName_AfterUpdate()
lblDate.Caption = Format(Now, "mm:dd:yyyy") & "; " & Format(Now, "hh:mm:ss")
End Sub



format(now,"mm:dd:yyyy\; hh:mm:ss")

Emoncada
04-09-2007, 08:59 AM
That works great. Thanks I don't want to change the topic but now that you have the code How can i make a TxtServerName become a link so when on the spreadsheet if clicked on it will bring up the form with all the data from that line. Is that possible? Or should I open a new thread?

Bob Phillips
04-09-2007, 09:02 AM
This is what I got
Private Sub CommandButton1_Click()
TxtFinalStatus = "Complete"
End Sub

Private Sub CommandButton2_Click()

'Add New Button to your UserForm so you press to Apply Data to Sheet
Dim RowNext As Integer

'last row of data puls one row
RowNext = Worksheets("Server Build Template").Cells(65536, 1).End(xlUp).Row + 1

'Cells(Row Number,Column Number)
With Worksheets("Server Build Template")
.Cells(RowNext, 1) = TxtName.Value
.Cells(RowNext, 2) = LblDate.Caption
.Cells(RowNext, 3) = TxtServerName.Value
.Cells(RowNext, 4) = TxtLocation.Value

If Me.ButtonG4 = True Then .Cells(RowNext, 5) = Me.LblML370G4.Caption
If Me.ButtonG5 = True Then .Cells(RowNext, 5) = Me.LblML370G5.Caption

.Cells(RowNext, 6) = TxtCtsContact.Value
.Cells(RowNext, 7) = TxtTracking.Value

If Me.Button72 = True Then .Cells(RowNext, 8) = Me.Lbl728GB.Caption
If Me.Button146 = True Then .Cells(RowNext, 8) = Me.Lbl146GB.Caption

.Cells(RowNext, 9) = TxtDrive1SN.Value
.Cells(RowNext, 10) = TxtDrive2SN.Value
.Cells(RowNext, 11) = TxtDrive3SN.Value
.Cells(RowNext, 12) = TxtDrive4SN.Value
.Cells(RowNext, 13) = TxtFinalStatus.Value
.Cells(RowNext, 14) = TxtSignature.Value
End With
ServerBuild.PrintForm

'You can't save a worksheet
ActiveWorkbook.Save

End Sub

Private Sub CommandButton3_Click()
ServerBuild.Hide
End Sub

Private Sub CommandButton4_Click()
TxtName.Value = ""
LblDate.Caption = ""
TxtServerName.Value = ""
TxtLocation.Value = ""
TxtCtsContact.Value = ""
TxtTracking.Value = ""
Me.ButtonG4 = False
Me.ButtonG5 = False
Me.Button146 = False
Me.Button72 = False
TxtDrive1SN.Value = ""
TxtDrive2SN.Value = ""
TxtDrive3SN.Value = ""
TxtDrive4SN.Value = ""
TxtFinalStatus.Value = ""
TxtSignature.Value = ""
TxtName.SetFocus

End Sub

Private Sub CommandButton5_Click()
TxtSignature = TxtName.Value
End Sub

Private Sub TxtName_AfterUpdate()
LblDate.Caption = Format(Now, "mm/dd/yyyy") & " " & Format(Now, "hh:mm:ss AM/PM")
End Sub

It is useful to name your buttons, like cmdClear, so as to see which is which.



Private mReEntryFlag As Boolean

Private Sub CommandButton1_Click()
TxtFinalStatus = "Complete"
End Sub

Private Sub CommandButton2_Click()

'Add New Button to your UserForm so you press to Apply Data to Sheet
Dim RowNext As Integer

'last row of data puls one row
RowNext = Worksheets("Server Build Template").Cells(65536, 1).End(xlUp).Row + 1

'Cells(Row Number,Column Number)
With Worksheets("Server Build Template")
.Cells(RowNext, 1) = TxtName.Value
.Cells(RowNext, 2) = LblDate.Caption
.Cells(RowNext, 3) = TxtServerName.Value
.Cells(RowNext, 4) = TxtLocation.Value

If Me.ButtonG4 = True Then .Cells(RowNext, 5) = Me.LblML370G4.Caption
If Me.ButtonG5 = True Then .Cells(RowNext, 5) = Me.LblML370G5.Caption

.Cells(RowNext, 6) = TxtCtsContact.Value
.Cells(RowNext, 7) = TxtTracking.Value

If Me.Button72 = True Then .Cells(RowNext, 8) = Me.Lbl728GB.Caption
If Me.Button146 = True Then .Cells(RowNext, 8) = Me.Lbl146GB.Caption

.Cells(RowNext, 9) = TxtDrive1SN.Value
.Cells(RowNext, 10) = TxtDrive2SN.Value
.Cells(RowNext, 11) = TxtDrive3SN.Value
.Cells(RowNext, 12) = TxtDrive4SN.Value
.Cells(RowNext, 13) = TxtFinalStatus.Value
.Cells(RowNext, 14) = TxtSignature.Value
End With
ServerBuild.PrintForm

'You can't save a worksheet
ActiveWorkbook.Save

End Sub

Private Sub CommandButton3_Click()
ServerBuild.Hide
End Sub

Private Sub CommandButton4_Click()
If Not mReEntryFlag Then
mReEntryFlag = True
TxtName.Value = ""
LblDate.Caption = ""
TxtServerName.Value = ""
TxtLocation.Value = ""
TxtCtsContact.Value = ""
TxtTracking.Value = ""
Me.ButtonG4 = False
Me.ButtonG5 = False
Me.Button146 = False
Me.Button72 = False
TxtDrive1SN.Value = ""
TxtDrive2SN.Value = ""
TxtDrive3SN.Value = ""
TxtDrive4SN.Value = ""
TxtFinalStatus.Value = ""
TxtSignature.Value = ""
TxtName.SetFocus
mReEntryFlag = True
End If
End Sub

Private Sub CommandButton5_Click()
TxtSignature = TxtName.Value
End Sub

Private Sub TxtName_AfterUpdate()
If Not mReEntryFlag Then
mReEntryFlag = True
LblDate.Caption = Format(Now, "mm/dd/yyyy\; hh:mm:ss AM/PM")
mReEntryFlag = True
End If
End Sub

mdmackillop
04-09-2007, 09:05 AM
Worth a new thread. Can you also post some data with your form.

Emoncada
04-09-2007, 09:07 AM
oK i Will post a new thread thanks for your help on this matter. Appreciate it.