automatic entry of data into a data entry form new

[Grahamscown]

is there a way to auto populate fields in a data entry form like seen below i would like to enter just the name and have the rego and company auto populate from a list and time . date , month and week auto populate at the same time of entry

 

[Fluff]

You need to do that in the VB Editor, not Excel.

 

[Grahamscown]

This is what I see when I click insert

Sorry I worked that out I did not have visual basic editor open

 

[Fluff]

 

[Grahamscown]

Thankyou I will update my account details when I return home later from work

 

[Grahamscown]

hi this is as far as i have got so far the close script works but the add script is not adding the data to the sheet the time and date buttons dont do anything at the moment as i want to try to auto populate them when the form is started

 

[Fluff]

Can you post your actual code rather than just an image. How to Post Your VBA Code
Also do you get any errors when you click the button?

 

[Grahamscown]

Can you post your actual code rather than just an image. How to Post Your VBA Code
Also do you get any errors when you click the button?

no i did not get any errors and i cant find the vba button to record the code

 

[Grahamscown]

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sameday")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

'check for a date
If Trim(Me.txtdate.Value) = "" Then
  Me.txtdate.SetFocus
  MsgBox "Please enter a date"
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(iRow, 1).Value = Me.txttime.Value
  .Cells(iRow, 2).Value = Me.txtname.Value
  .Cells(iRow, 3).Value = Me.txtrego.Value
  .Cells(iRow, 4).Value = Me.txtcompany.Value
  .Cells(iRow, 5).Value = Me.txtdate.Value
  .Cells(iRow, 6).Value = Me.txtmonth.Value
  .Cells(iRow, 7).Value = Me.txtweek.Value
  '  .Protect Password:="password"
End With

'clear the data
Me.txttime.Value = ""
Me.txtname.Value = ""
Me.txtrego.Value = ""
Me.txtcompany.Value = ""
Me.txtname.Date = ""
Me.txtrego.Month = ""
Me.txtcompany.week = ""
Me.txttime.SetFocus
End Sub


 Private Sub cmdClose_Click()
  Unload Me
End Sub

Private Sub txtcompany_Change()

End Sub

Private Sub txtdate_Change()

End Sub

Private Sub txtmonth_Change()

End Sub

Private Sub txtname_Change()

End Sub

Private Sub txtrego_Change()

End Sub

Private Sub txtweek_Change()

End Sub

Private Sub UserForm_Click()

End Sub

 

[Fluff]

Ok, thanks for that.
If you add this msgbox as shown & click the button, what does the message return

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
MsgBox iRow
'check for a date

 

[Grahamscown]

Ok, thanks for that.
If you add this msgbox as shown & click the button, what does the message return

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
MsgBox iRow
'check for a date

sorry where will i add it to the bottom or top