adding a date into a specific cell when updating

[Mark_richardson28]

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.

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

'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]

 

[Joe Was]

Every test you have in the posted code is checking for Blank. So if you want to test at the end for box's that can be blank and are not mandatory, set up a test that checks these for "Value" being Blank.

Now yoy can set a Value to:

Application.WorksheetFunction.Text(Date, "m/d/yy") and add it or cancantanate it to your data. You can pick any date format you want!