I'm using it so a user can input 11 values into text boxes that will then date stamp the data and chuck it into the spreadsheet that im using as a database. The txtprompts is the value that determines how many of the others i can have. ie if they get 10 prompts they can only do something with the 10 prompts
Me.txttas + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value
donates the 6 ways the first number can be divided.
so when everything is updated i need the macro to determine that there is something not correct in the validation if everything doesn't add up.
Here is the code i have for the full thing so far,
Private Sub cmdupdate_Click()
Application.ScreenUpdating = False
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Raw Data - Jan Martin")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) = Date
'check for calls
If txtcalls.Text = "" Then Call BadInput("NoSource"): Exit Sub
If Trim(Me.txtcalls.Value) = "" Then
Me.txtcalls.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 2).Value = Me.txtcalls.Value
'clear the data
Me.txtcalls.Value = ""
Me.txtcalls.SetFocus
'check for prompts
If txtprompts.Text = "" Then Call BadInput("slacker"): Exit Sub
If Trim(Me.txtprompts.Value) = "" Then
Me.txtprompts.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 3).Value = Me.txtprompts.Value
'clear the data
Me.txtprompts.Value = ""
Me.txtprompts.SetFocus
'check for live leads
If Me.txtlivelead.Value = "" Then
Me.txtlivelead.Value = 0
End If
If Trim(Me.txtlivelead.Value) = "" Then
Me.txtlivelead.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 4).Value = Me.txtlivelead.Value
'clear the data
Me.txtlivelead.Value = ""
Me.txtlivelead.SetFocus
'check for succesful to TAS
If Me.txttas.Value = "" Then
Me.txttas.Value = 0
End If
If Trim(Me.txttas.Value) = "" Then
Me.txttas.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 5).Value = Me.txttas.Value
'clear the data
Me.txttas.Value = ""
Me.txttas.SetFocus
'check for succesful OOH
If Me.txtooh.Value = "" Then
Me.txtooh.Value = 0
End If
If Trim(Me.txtooh.Value) = "" Then
Me.txtooh.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 6).Value = Me.txtooh.Value
'clear the data
Me.txtooh.Value = ""
Me.txtooh.SetFocus
'check for succesful engaged
If Trim(Me.txtengaged.Value) = "" Then
Me.txtengaged.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 7).Value = Me.txtengaged.Value
'clear the data
Me.txtengaged.Value = ""
Me.txtengaged.SetFocus
'check for succesful other
If Me.txtother.Value = "" Then
Me.txtother.Value = 0
End If
If Trim(Me.txtother.Value) = "" Then
Me.txtother.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 8).Value = Me.txtother.Value
'clear the data
Me.txtother.Value = ""
Me.txtother.SetFocus
'check for succesful live lead
If Me.txtliveleadsuccesful.Value = "" Then
Me.txtliveleadsuccesful.Value = 0
End If
If Trim(Me.txtliveleadsuccesful.Value) = "" Then
Me.txtliveleadsuccesful.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 9).Value = Me.txtliveleadsuccesful.Value
'clear the data
Me.txtliveleadsuccesful.Value = ""
Me.txtliveleadsuccesful.SetFocus
'check for recycled
If Me.txtrecycled.Value = "" Then
Me.txtrecycled.Value = 0
End If
If Trim(Me.txtrecycled.Value) = "" Then
Me.txtrecycled.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 10).Value = Me.txtrecycled.Value
'clear the data
Me.txtrecycled.Value = ""
Me.txtrecycled.SetFocus
'check for closed
If Me.txtclosed.Value = "" Then
Me.txtclosed.Value = 0
End If
If Trim(Me.txtclosed.Value) = "" Then
Me.txtclosed.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 11).Value = Me.txtclosed.Value
'clear the data
Me.txtclosed.Value = ""
Me.txtclosed.SetFocus
'check for lifestyle
If Me.txtlifestyle.Value = "" Then
Me.txtlifestyle.Value = 0
End If
If Trim(Me.txtlifestyle.Value) = "" Then
Me.txtlifestyle.SetFocus
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 12).Value = Me.txtlifestyle.Value
'clear the data
Me.txtlifestyle.Value = ""
Me.txtlifestyle.SetFocus
' Make sure everything is ok
If Me.txttas.Value + Me.txtooh.Value + Me.txtengaged.Value + Me.txtother.Value + Me.txtrecycled.Value + Me.txtclosed.Value <> Me.txtprompts.Value Then
Call BadInput("nocompute"): Exit Sub
End If
' Close the Form Box
frmjanmartin.Hide
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If
' confirm update complete
a = MsgBox("Thank You for filling in the personal prompt database - Update complete", vbOKOnly, "Direct Banking Edinburgh")
End Sub