Is There a Way to keep the same Date and Data in the same row on a worksheet with this code below?. I am trying to update a Hourly Count Sheet and the end users of the form need to close the program and re-enter the data for the next hour. on a daily bases. Is this possible?. Thank You. I have included The Module in this code.
Code
Option Explicit
Private Sub cmdReset_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to Reset your counts", vbYesNo + vbInformation, "Hourly Space Counts")
If msgValue = vbNo Then Exit Sub
Call Reset
End Sub
Private Sub cmdSave_Click()
Dim msgValue As VbMsgBoxResult
msgValue = MsgBox("Do you want to save your counts?", vbYesNo + vbInformation, "Hourly Space Counts")
If msgValue = vbNo Then Exit Sub
Call Submit
End Sub
Private Sub UserForm_Activate()
Dim C As Boolean
Do
If C = True Then Exit Sub
ClockTextBox = Format(Now, "HH:MM:SS")
DoEvents
Loop
End Sub
Private Sub UserForm_Initialize()
lblDate1 = Format(Date, "mm/d/yyyy")
lblDate2 = Format(Date, "mmmm d, yyyy")
Call Reset
End Sub
"Module
Option Explicit
Sub Reset()
Dim iRow As Long
iRow = [CountA(Database!A:A)] 'idetifying the last row
With frmForm
.txtRowNumber.Value = ""
.lstDatabase.ColumnCount = 145
.lstDatabase.ColumnHeads = True
.lstDatabase.ColumnWidths = "125 ,91 ,91,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91 ,91,91,91,91,91,91"
If iRow > 1 Then
.lstDatabase.RowSource = "Database!A2: EO" & iRow
Else
.lstDatabase.RowSource = "Database!A2:EO2"
End If
End With
End Sub
Sub Submit()
Dim sh As Worksheet
Dim iRow As Long
Set sh = ThisWorkbook.Sheets("Database")
iRow = [CountA(Database!A:A)] + 1
With sh
.Cells(iRow, 2) = iRow - 1 'This Is The Dynamic Serial Number
.Cells(iRow, 1) = frmForm.lblDate1.Value
.Cells(iRow, 2) = frmForm.txt0000ABC.Value
.Cells(iRow, 3) = frmForm.txt0000ConRac.Value
.Cells(iRow, 4) = frmForm.txt0000P4.Value
.Cells(iRow, 5) = frmForm.txt0000P6.Value
.Cells(iRow, 6) = frmForm.txt0000Valet.Value
.Cells(iRow, 7) = frmForm.txt0000LotF.Value
.Cells(iRow, 8) = frmForm.txt0100ABC.Value
.Cells(iRow, 9) = frmForm.txt0100ConRac.Value
.Cells(iRow, 10) = frmForm.txt0100P4.Value
.Cells(iRow, 11) = frmForm.txt0100P6.Value
.Cells(iRow, 12) = frmForm.txt0100Valet.Value
.Cells(iRow, 13) = frmForm.txt0100LotF.Value
.Cells(iRow, 14) = frmForm.txt0200ABC.Value
.Cells(iRow, 15) = frmForm.txt0200ConRac.Value
.Cells(iRow, 16) = frmForm.txt0200P4.Value
.Cells(iRow, 18) = frmForm.txt0200P6.Value
.Cells(iRow, 17) = frmForm.txt0200Valet.Value
.Cells(iRow, 18) = frmForm.txt0200LotF.Value
.Cells(iRow, 19) = frmForm.txt0300ABC.Value
.Cells(iRow, 20) = frmForm.txt0300ConRac.Value
.Cells(iRow, 21) = frmForm.txt0300P4.Value
.Cells(iRow, 22) = frmForm.txt0300P6.Value
.Cells(iRow, 23) = frmForm.txt0300Valet.Value
.Cells(iRow, 24) = frmForm.txt0300LotF.Value
.Cells(iRow, 25) = frmForm.txt0400ABC.Value
.Cells(iRow, 26) = frmForm.txt0400ConRac.Value
.Cells(iRow, 27) = frmForm.txt0400P4.Value
.Cells(iRow, 28) = frmForm.txt0400P6.Value
.Cells(iRow, 29) = frmForm.txt0400Valet.Value
.Cells(iRow, 30) = frmForm.txt0400LotF.Value
.Cells(iRow, 31) = frmForm.txt0500ABC.Value
.Cells(iRow, 32) = frmForm.txt0500ConRac.Value
.Cells(iRow, 33) = frmForm.txt0500P4.Value
.Cells(iRow, 34) = frmForm.txt0500P6.Value
.Cells(iRow, 35) = frmForm.txt0500Valet.Value
.Cells(iRow, 36) = frmForm.txt0500LotF.Value
.Cells(iRow, 37) = frmForm.txt0600ABC.Value
.Cells(iRow, 38) = frmForm.txt0600ConRac.Value
.Cells(iRow, 39) = frmForm.txt0600P4.Value
.Cells(iRow, 40) = frmForm.txt0600P6.Value
.Cells(iRow, 41) = frmForm.txt0600Valet.Value
.Cells(iRow, 42) = frmForm.txt0600LotF.Value
.Cells(iRow, 43) = frmForm.txt0600ABC.Value
.Cells(iRow, 44) = frmForm.txt0600ConRac.Value
.Cells(iRow, 45) = frmForm.txt0600P4.Value
.Cells(iRow, 46) = frmForm.txt0600P6.Value
.Cells(iRow, 47) = frmForm.txt0600Valet.Value
.Cells(iRow, 48) = frmForm.txt0600LotF.Value
End With
End Sub
Sub Show_From()
frmForm.Show
End Sub
Display More