Hello. I have been trying to figure this out for the life of me, but i can't quite wrap my head around it. I am somewhat new to VB so please bare with me. I work for a company that records and produces audio books. I have been tasked with designing a datbase with a user form that our recording monitors can fill out that tracks the books we are reading. Some background is necessary here:
Each audio file we record is called a "Side" (its a reference to the old days of tape). A Side is 88 minutes long. The user form I created tracks the Side number, Start Time, and End Time of the session. For example, in a typical session you may begin recording the narrator on side 3, at 15 minutes;, and end the session on side 3, at 76 minutes.
The form I created, has mostly combo boxes on it to select the log-in times and other information, and uses some simple vb that i adapted from a tutorial to copy about 16 fields of data onto the next empty row of the log that rests in a hidden sheet called "MinLog". In column A is the Book Title, and in column F:H is the side number, start time, and end time respectively.
The issue is this: We want to be able to use this form for payroll and other important things, so user error with the form needs to be eliminated. I have already added the code that returns a message box and ends the sub if any of the required fields are missing. However, I also need it to return an error if they enter the incorrect side number, start time or end time.
To be more specific, i need the form to check the following general rules before it allows the user to save the data:
It needs to find the previous entries from the last users that entered data for the same book title they are working on. In other words, it needs to filter column A to find the correct book title. Then it needs to sort each row to get the correct sequential order of the sessions. I assume the next step would be to sort the filtered data by ascending side number, and then by ascending start time number. Typically there is 1-3 logins per side.
For example the side/start/end times for a book might look like this:
Started on side 1, start time 0, end time 56.
Next session: side 1, start time 56, end time 88.
Next session: side 2, start time 0, end time 77.
Next Session: side 2, start time 77, end time 88.
With that in mind....
1) The start of a session should always be equal to the end time of the previous session, unless the end time of the previous session = 88, in which case the start time of the next session must always be o.
2) If the end time equals 88, then the next session's side number must be equal to the previous side number plus one. If the end time does not equal 88, then the next session's side number must be equal to the previous session's side number.
3) The first session of the book should always be on side 1, with a start time of 0
These 3 rules i beleive should catch all possible mistakes. As a temporary fix, i've developed a macro that checks these rules after the data has been entered and finds all the errors using the excel IF formula. but its going to create too much extra work to have to regularly validate each book, and manually fix them.... we do about 500 sides a month, so that can get a bit tedious.
I need the user form to prevent the data from being saved if those conditions aren't met. We have about 30 users and the potential for mistakes is inevitable if i can't control the problem at the source.
I know I need some kind of IF statement with VB but i am not versed well enough in the syntax to figure it out. I've tried googling around but all the examples of user form possibilities seem to not apply to my situation
I've attached my excel doc with some dummy data and pasted the form code below.
I apprecitae any help... thanks.
JRB
Note: This isn't letting post attachments but i will reply with a link to the excel file in the morning... thanks.
<input id="gwProxy" type="hidden"><!--Session data--><input *******="jsCall();" id="jsProxy" type="hidden">
Each audio file we record is called a "Side" (its a reference to the old days of tape). A Side is 88 minutes long. The user form I created tracks the Side number, Start Time, and End Time of the session. For example, in a typical session you may begin recording the narrator on side 3, at 15 minutes;, and end the session on side 3, at 76 minutes.
The form I created, has mostly combo boxes on it to select the log-in times and other information, and uses some simple vb that i adapted from a tutorial to copy about 16 fields of data onto the next empty row of the log that rests in a hidden sheet called "MinLog". In column A is the Book Title, and in column F:H is the side number, start time, and end time respectively.
The issue is this: We want to be able to use this form for payroll and other important things, so user error with the form needs to be eliminated. I have already added the code that returns a message box and ends the sub if any of the required fields are missing. However, I also need it to return an error if they enter the incorrect side number, start time or end time.
To be more specific, i need the form to check the following general rules before it allows the user to save the data:
It needs to find the previous entries from the last users that entered data for the same book title they are working on. In other words, it needs to filter column A to find the correct book title. Then it needs to sort each row to get the correct sequential order of the sessions. I assume the next step would be to sort the filtered data by ascending side number, and then by ascending start time number. Typically there is 1-3 logins per side.
For example the side/start/end times for a book might look like this:
Started on side 1, start time 0, end time 56.
Next session: side 1, start time 56, end time 88.
Next session: side 2, start time 0, end time 77.
Next Session: side 2, start time 77, end time 88.
With that in mind....
1) The start of a session should always be equal to the end time of the previous session, unless the end time of the previous session = 88, in which case the start time of the next session must always be o.
2) If the end time equals 88, then the next session's side number must be equal to the previous side number plus one. If the end time does not equal 88, then the next session's side number must be equal to the previous session's side number.
3) The first session of the book should always be on side 1, with a start time of 0
These 3 rules i beleive should catch all possible mistakes. As a temporary fix, i've developed a macro that checks these rules after the data has been entered and finds all the errors using the excel IF formula. but its going to create too much extra work to have to regularly validate each book, and manually fix them.... we do about 500 sides a month, so that can get a bit tedious.
I need the user form to prevent the data from being saved if those conditions aren't met. We have about 30 users and the potential for mistakes is inevitable if i can't control the problem at the source.
I know I need some kind of IF statement with VB but i am not versed well enough in the syntax to figure it out. I've tried googling around but all the examples of user form possibilities seem to not apply to my situation
I've attached my excel doc with some dummy data and pasted the form code below.
I apprecitae any help... thanks.
JRB
Note: This isn't letting post attachments but i will reply with a link to the excel file in the morning... thanks.
Code:
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdSave_Click()
Dim lRow As Long
Dim lTitle As Long
Dim ws As Worksheet
Set ws = Worksheets("MinLog")
'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
lTitle = Me.cboTitle.ListIndex
'check for a book title
If Trim(Me.cboTitle.Value) = "" Then
Me.cboTitle.SetFocus
MsgBox "Please enter a book title"
Exit Sub
End If
If Trim(Me.txtDate.Value) = "" Then
Me.txtDate.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If
If Trim(Me.cboStudio.Value) = "" Then
Me.cboSide.SetFocus
MsgBox "Please enter a Studio."
Exit Sub
End If
If Trim(Me.cboSide.Value) = "" Then
Me.cboSide.SetFocus
MsgBox "Please enter a side number."
Exit Sub
End If
If Trim(Me.cboStart.Value) = "" Then
Me.cboStart.SetFocus
MsgBox "Please enter a start time."
Exit Sub
End If
If Trim(Me.cboEnd.Value) = "" Then
Me.cboEnd.SetFocus
MsgBox "Please enter an end time."
Exit Sub
End If
If Trim(Me.cboEnd.Value) <= Trim(Me.cboStart.Value) Then
Me.cboEnd.SetFocus
MsgBox "Your end time is earlier than your start time. Please try again."
Exit Sub
End If
If Trim(Me.cboStatus.Value) = "" Then
Me.cboStatus.SetFocus
MsgBox "Please enter the book status."
Exit Sub
End If
If Trim(Me.cboMonitor.Value) = "" Then
Me.cboMonitor.SetFocus
MsgBox "Please enter monitor initials."
Exit Sub
End If
If Trim(Me.cboNarrator.Value) = "" Then
Me.cboNarrator.SetFocus
MsgBox "Please enter a narrator name."
Exit Sub
End If
If Trim(Me.cboNarrator.Value) = "" Then
Me.cboNarrator.SetFocus
MsgBox "Please enter a narrator name."
Exit Sub
End If
'copy the data to the database
With ws
.Cells(lRow, 1).Value = Me.cboTitle.Value
.Cells(lRow, 2).Value = Me.cboTitle.List(lTitle, 1)
.Cells(lRow, 3).Value = Me.txtDate.Value
.Cells(lRow, 4).Value = Me.cboStudio.Value
.Cells(lRow, 5).Value = Me.chkMultiple.Value
.Cells(lRow, 6).Value = Me.cboSide.Value
.Cells(lRow, 7).Value = Me.cboStart.Value
.Cells(lRow, 8).Value = Me.cboEnd.Value
.Cells(lRow, 9).Value = Me.cboStatus.Value
.Cells(lRow, 10).Value = Me.cboType.Value
.Cells(lRow, 11).Value = Me.cboLanguage.Value
.Cells(lRow, 12).Value = Me.txtFormat.Value
.Cells(lRow, 13).Value = Me.txtTech.Value
.Cells(lRow, 14).Value = Me.txtNotes.Value
.Cells(lRow, 15).Value = Me.cboMonitor.Value
.Cells(lRow, 16).Value = Me.cboNarrator.Value
End With
'clear the data
Me.cboTitle.Value = ""
Me.txtDate.Value = Format(Date, "Medium Date")
Me.cboSide.Value = ""
Me.cboStart.Value = ""
Me.cboEnd.Value = ""
Me.cboStudio.Value = "A"
Me.cboType.Value = "Standard"
Me.cboLanguage.Value = "English"
Me.txtFormat.Value = ""
Me.txtTech.Value = ""
Me.txtNotes.Value = ""
Me.cboMonitor.Value = ""
Me.cboNarrator.Value = ""
Me.cboTitle.SetFocus
Unload Me
ActiveWorkbook.Save
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Label10_Click()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
Dim cTitle As Range
Dim cSide As Range
Dim cStart As Range
Dim cEnd As Range
Dim cStatus As Range
Dim cType As Range
Dim cStudio As Range
Dim cMonitor As Range
Dim cNarrator As Range
Dim cLanguage As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
For Each cTitle In ws.Range("BookTitleList")
With Me.cboTitle
.AddItem cTitle.Value
.List(.ListCount - 1, 1) = cTitle.Offset(0, 1).Value
End With
Next cTitle
For Each cSide In ws.Range("SideList")
With Me.cboSide
.AddItem cSide.Value
End With
Next cSide
For Each cStart In ws.Range("StartList")
With Me.cboStart
.AddItem cStart.Value
End With
Next cStart
For Each cEnd In ws.Range("EndList")
With Me.cboEnd
.AddItem cEnd.Value
End With
Next cEnd
For Each cStatus In ws.Range("StatusList")
With Me.cboStatus
.AddItem cStatus.Value
End With
Next cStatus
For Each cType In ws.Range("TypeList")
With Me.cboType
.AddItem cType.Value
End With
Next cType
For Each cStudio In ws.Range("StudioList")
With Me.cboStudio
.AddItem cStudio.Value
End With
Next cStudio
For Each cLanguage In ws.Range("LanguageList")
With Me.cboLanguage
.AddItem cLanguage.Value
End With
Next cLanguage
For Each cMonitor In ws.Range("MonitorList")
With Me.cboMonitor
.AddItem cMonitor.Value
End With
Next cMonitor
For Each cNarrator In ws.Range("NarratorList")
With Me.cboNarrator
.AddItem cNarrator.Value
End With
Next cNarrator
Me.txtDate.Value = Format(Date, "Medium Date")
Me.cboStudio.Value = "A"
Me.cboType.Value = "Standard"
Me.cboLanguage.Value = "English"
Me.cboStatus.Value = ""
Me.txtFormat.Value = ""
Me.txtTech.Value = ""
Me.txtNotes.Value = ""
Me.cboMonitor.Value = ""
Me.cboTitle.SetFocus
End Sub
