Mark_richardson28
New Member
- Joined
- Sep 29, 2006
- Messages
- 36
I am using excel as a database. The user inserts the info via a form box with text boxes.
The code i am using finds the next available line on the database before inputting the users preference. The code is below.
'copy the data to the database
ws.Cells(iRow, 12).Value = Me.txtlifestyle.Value
'clear the data
Me.txtlifestyle.Value = ""
Me.txtlifestyle.SetFocus
' Close the Form Box
frmjanmartin.Hide
' confirm update complete
a = MsgBox("Thank You for filling in the personal prompt database - Update complete", vbOKOnly, "Direct Banking Edinburgh")
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If
End Sub[/code]
I need to be able to create a date input into the cells a, referencing to the data that is imput by the user.
If anyone could help with this i'd be mucho grateful.
I have one other query.
If the user doesn't type anything in the text boxes it wont update the spreadsheet. is there anything i can add to the code that will eliminate non input errors?[/code]
The code i am using finds the next available line on the database before inputting the users preference. The code is below.
Code:
Private Sub cmdupdate_Click()
Application.ScreenUpdating = False
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("RawData")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
If iRow = ws.Cells(Rows.Count, 2) Then
ws.Cells(1, 0).Value = today()
End If
'check for calls
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 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 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 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 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 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 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 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 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 Trim(Me.txtlifestyle.Value) = "" Then
Me.txtlifestyle.SetFocus
Exit Sub
End If
ws.Cells(iRow, 12).Value = Me.txtlifestyle.Value
'clear the data
Me.txtlifestyle.Value = ""
Me.txtlifestyle.SetFocus
' Close the Form Box
frmjanmartin.Hide
' confirm update complete
a = MsgBox("Thank You for filling in the personal prompt database - Update complete", vbOKOnly, "Direct Banking Edinburgh")
If ThisWorkbook.Saved = False Then
ThisWorkbook.Save
End If
End Sub[/code]
I need to be able to create a date input into the cells a, referencing to the data that is imput by the user.
If anyone could help with this i'd be mucho grateful.
I have one other query.
If the user doesn't type anything in the text boxes it wont update the spreadsheet. is there anything i can add to the code that will eliminate non input errors?[/code]