adding a date into a specific cell when updating

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.


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

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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!
 

Forum statistics

Threads
1,136,266
Messages
5,674,727
Members
419,523
Latest member
Urnovio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top