a code to transfer dataentry from userform to worksheet within same workbook

T

Timothy Ibe

New Member
Joined
Jun 15, 2022
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends,

Please, I need help. I designed a userform, I need the code that can be used to transfer entries from the dataentry form I designed to the "Database" sheet on the same work book.

Please friends, help me. It is urgent. And I am stuck here. The name of the USERFORM is "frmdataentry", the name of the Worksheet is "Database" (All fields inputed already)

Thank you.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
Below is the code, pls help.
VBA Code: 
        irow = .Range("A" & Application.Rows.Count).End(xlUp).Row + 1
       
        .Range("A" & irow).Value = irow - 1
       
        .Range("B" & irow).Value = FrmDataEntry.cmbTitle.Value
       
        .Range("C" & irow).Value = FrmDataEntry.txtSurname.Value
       
        .Range("D" & irow).Value = FrmDataEntry.txtFirstName.Value
       
        .Range("E" & irow).Value = FrmDataEntry.txtOtherName.Value
       
        .Range("F" & irow).Value = FrmDataEntry.txtMaidenName.Value
       
        .Range("G" & irow).Value = FrmDataEntry.txtDateofBirth.Value
       
        .Range("H" & irow).Value = FrmDataEntry.cmbmaritalstatus.Value
       
        .Range("I" & irow).Value = FrmDataEntry.txtPlaceofBirth.Value
       
        .Range("J" & irow).Value = IIf(FrmDataEntry.optFemale.Value = True, "Female", "Male")
       
        .Range("K" & irow).Value = FrmDataEntry.txtimagepath.Value
       
        .Range("L" & irow).Value = FrmDataEntry.txtNationality.Value
       
        .Range("M" & irow).Value = FrmDataEntry.cmbStateofOrigin.Value
       
        .Range("N" & irow).Value = FrmDataEntry.txtSenatorialDistrict.Value
       
        .Range("O" & irow).Value = FrmDataEntry.txtLocalGovtofOrigin.Value
       
        .Range("P" & irow).Value = FrmDataEntry.txtWard.Value
       
        .Range("Q" & irow).Value = FrmDataEntry.txtnextofkinfullname.Value
       
        .Range("R" & irow).Value = FrmDataEntry.txtRelationship.Value
       
        .Range("S" & irow).Value = FrmDataEntry.txtnextofkinresidentialaddress.Value
       
        .Range("T" & irow).Value = FrmDataEntry.txtTelephone.Value
       
        .Range("U" & irow).Value = FrmDataEntry.txtMobileTelephone.Value
       
        .Range("V" & irow).Value = FrmDataEntry.cmbAppointment.Value
       
        .Range("W" & irow).Value = FrmDataEntry.txtPresentRank.Value
       
        .Range("X" & irow).Value = FrmDataEntry.txtCurrentGradeLevel.Value
    
        .Range("Y" & irow).Value = FrmDataEntry.txtDateofFirstAppointment.Value
       
        .Range("Z" & irow).Value = FrmDataEntry.txtstep.Value
     
        .Range("AA" & irow).Value = FrmDataEntry.txtDateofConfirmation.Value
       
        .Range("AB" & irow).Value = FrmDataEntry.txtdateofpresentappointment.Value
       
        .Range("AC" & irow).Value = FrmDataEntry.txtcomputernopsm.Value
       
        .Range("AD" & irow).Value = FrmDataEntry.txtestabno.Value
       
        .Range("AE" & irow).Value = FrmDataEntry.txtemploymentno.Value
       
        .Range("AF" & irow).Value = FrmDataEntry.txtpersonalfileno.Value
       
        .Range("AG" & irow).Value = FrmDataEntry.txtTaxIDNo.Value
       
        .Range("AH" & irow).Value = FrmDataEntry.cmbidtype.Value
       
        .Range("AI" & irow).Value = FrmDataEntry.txtidtype.Value
       
        .Range("AJ" & irow).Value = FrmDataEntry.txtissuingauthority.Value
       
        .Range("AK" & irow).Value = FrmDataEntry.txtdateissued.Value
       
        .Range("AL" & irow).Value = FrmDataEntry.txtinstattended.Value
       
        .Range("AM" & irow).Value = FrmDataEntry.txtaddress.Value
       
        .Range("AN" & irow).Value = FrmDataEntry.txtfrom.Value
       
        .Range("AO" & irow).Value = FrmDataEntry.txtto.Value
       
        .Range("AP" & irow).Value = FrmDataEntry.txtcertificateobtained.Value
       
        .Range("AQ" & irow).Value = FrmDataEntry.txtRegLicenseNo.Value
       
        .Range("AR" & irow).Value = FrmDataEntry.txtissingat.Value
       
        .Range("AS" & irow).Value = FrmDataEntry.txtdataiss.Value
       
        .Range("AT" & irow).Value = FrmDataEntry.txtexpirydate.Value
       
        .Range("AU" & irow).Value = FrmDataEntry.txtspecialty.Value
       
        .Range("AV" & irow).Value = FrmDataEntry.cmbcadre.Value
       
        .Range("AW" & irow).Value = FrmDataEntry.txtadditionalprofessionalskills.Value
       
        .Range("AX" & irow).Value = FrmDataEntry.txtadress.Value
       
        .Range("AY" & irow).Value = FrmDataEntry.txtwad.Value
       
        .Range("AZ" & irow).Value = FrmDataEntry.txtlga.Value
       
        .Range("BA" & irow).Value = FrmDataEntry.txtsenatorialdst.Value
       
        .Range("BB" & irow).Value = FrmDataEntry.cmbstatee.Value
       
        .Range("BC" & irow).Value = FrmDataEntry.txtemail.Value
       
        .Range("BD" & irow).Value = FrmDataEntry.txttel.Value
       
        .Range("BE" & irow).Value = FrmDataEntry.txtmobiletel.Value
       
        .Range("BF" & irow).Value = FrmDataEntry.txtaddss.Value
       
        .Range("BG" & irow).Value = FrmDataEntry.txtwar.Value
       
        .Range("BH" & irow).Value = FrmDataEntry.txtlgga.Value
       
        .Range("BI" & irow).Value = FrmDataEntry.txtsendis.Value
       
        .Range("BJ" & irow).Value = FrmDataEntry.cmbstate.Value
       
        .Range("BK" & irow).Value = FrmDataEntry.txtemaill.Value
       
        .Range("BL" & irow).Value = FrmDataEntry.txttelll.Value
       
        .Range("BM" & irow).Value = FrmDataEntry.txtmobiletelll.Value
       
        .Range("BN" & irow).Value = FrmDataEntry.txtsubmittingEntry.Value
       
        .Range("BO" & irow).Value = FrmDataEntry.txtoffice.Value
       
        .Range("BP" & irow).Value = FrmDataEntry.cmbFacilitytype.Value
       
        .Range("BQ" & irow).Value = FrmDataEntry.cmblevelofcare.Value
       
        .Range("BR" & irow).Value = FrmDataEntry.txtdepartment.Value
       
        .Range("BS" & irow).Value = FrmDataEntry.txtunitname.Value
       
        .Range("BT" & irow).Value = FrmDataEntry.txtwd.Value
       
        .Range("BU" & irow).Value = FrmDataEntry.txtlgaa.Value
       
        .Range("BV" & irow).Value = FrmDataEntry.txtdistrictSen.Value
       
        .Range("BW" & irow).Value = FrmDataEntry.cmbstattee.Value
       
        .Range("BX" & irow).Value = FrmDataEntry.txtsignature.Value
       
        .Range("BY" & irow).Value = FrmDataEntry.txtlastdate.Value
               
        .Range("BZ" & irow).Value = Application.UserName
       
        .Range("CA" & irow).Value = Format([Now()], "DD-MM-YYYY HH:MM:SS")
       
    End With
   
    wBook.Close savechanges:=True
    App.Quit
    Set App = Nothing
   
    'Call Application
   
    Call Reset
   
   
    Application.ScreenUpdating = True
   
    MsgBox "Data Submitted Successfully!"
   

End Sub
 
Last edited by a moderator:
Upvote 0
Hi,

Apart from there seems to be some code missing in #post 2 & it’s rather long, perhaps if can explain a little further what the actual issue you are having with it as code looks like should work ok, maybe then forum will be able to assist you.

Dave
 
Upvote 0
Thank you so much Dave, for your response.
I had to change to the code below and it worked, but two textboxes in the dataentry form are return some strange digits in the database.

VBA Code: 
Sub Submit_Data()
      
    Dim irow As Long
          
        irow = shDatabase.Range("A" & Rows.Count).End(xlUp).Row + 1
   
    With shDatabase.Range("A" & irow)
       
        .Offset(0, 0).Value = "=Row()-1"
       
        .Offset(0, 1).Value = FrmDataEntry.cmbTitle.Value
       
        .Offset(0, 2).Value = FrmDataEntry.txtSurname.Value
       
        .Offset(0, 3).Value = FrmDataEntry.txtFirstName.Value
       
        .Offset(0, 4).Value = FrmDataEntry.txtOtherName.Value
       
        .Offset(0, 5).Value = FrmDataEntry.txtMaidenName.Value
       
        .Offset(0, 6).Value = FrmDataEntry.txtDateofBirth.Value
       
        .Offset(0, 7).Value = FrmDataEntry.cmbmaritalstatus.Value
       
        .Offset(0, 8).Value = FrmDataEntry.txtPlaceofBirth.Value
       
        .Offset(0, 9).Value = IIf(FrmDataEntry.optFemale.Value = True, "Female", "Male")
       
        .Offset(0, 10).Value = FrmDataEntry.txtimagepath.Value
       
        .Offset(0, 11).Value = FrmDataEntry.txtNationality.Value
       
        .Offset(0, 12).Value = FrmDataEntry.cmbStateofOrigin.Value
       
        .Offset(0, 13).Value = FrmDataEntry.txtSenatorialDistrict.Value
       
        .Offset(0, 14).Value = FrmDataEntry.txtLocalGovtofOrigin.Value
       
        .Offset(0, 15).Value = FrmDataEntry.txtWard.Value
       
        .Offset(0, 16).Value = FrmDataEntry.txtnextofkinfullname.Value
       
        .Offset(0, 17).Value = FrmDataEntry.txtRelationship.Value
       
        .Offset(0, 18).Value = FrmDataEntry.txtnextofkinresidentialaddress.Value
       
        .Offset(0, 19).Value = FrmDataEntry.txtTelephone.Value
       
        .Offset(0, 20).Value = FrmDataEntry.txtMobileTelephone.Value
       
        .Offset(0, 21).Value = FrmDataEntry.cmbAppointment.Value
       
        .Offset(0, 22).Value = FrmDataEntry.txtPresentRank.Value
       
        .Offset(0, 23).Value = FrmDataEntry.txtCurrentGradeLevel.Value
    
        .Offset(0, 24).Value = FrmDataEntry.txtDateofFirstAppointment.Value
       
        .Offset(0, 25).Value = FrmDataEntry.txtstep.Value
     
        .Offset(0, 26).Value = FrmDataEntry.txtDateofConfirmation.Value
       
        .Offset(0, 27).Value = FrmDataEntry.txtdateofpresentappointment.Value
       
        .Offset(0, 28).Value = FrmDataEntry.txtcomputernopsm.Value
       
        .Offset(0, 29).Value = FrmDataEntry.txtestabno.Value
       
        .Offset(0, 30).Value = FrmDataEntry.txtemploymentno.Value
       
        .Offset(0, 31).Value = FrmDataEntry.txtpersonalfileno.Value
       
        .Offset(0, 32).Value = FrmDataEntry.txtTaxIDNo.Value
       
        .Offset(0, 33).Value = FrmDataEntry.cmbidtype.Value
       
        .Offset(0, 34).Value = FrmDataEntry.txtidtype.Value
       
        .Offset(0, 35).Value = FrmDataEntry.txtissuingauthority.Value
       
        .Offset(0, 36).Value = FrmDataEntry.txtdateissued.Value
       
        .Offset(0, 37).Value = FrmDataEntry.txtinstattended.Value
       
        .Offset(0, 38).Value = FrmDataEntry.txtaddress.Value
       
        .Offset(0, 39).Value = FrmDataEntry.txtfrom.Value
       
        .Offset(0, 40).Value = FrmDataEntry.txtto.Value
       
        .Offset(0, 41).Value = FrmDataEntry.txtcertificateobtained.Value
       
        .Offset(0, 42).Value = FrmDataEntry.txtRegLicenseNo.Value
       
        .Offset(0, 43).Value = FrmDataEntry.txtissingat.Value
       
        .Offset(0, 44).Value = FrmDataEntry.txtdataiss.Value
       
        .Offset(0, 45).Value = FrmDataEntry.txtexpirydate.Value
       
        .Offset(0, 46).Value = FrmDataEntry.txtspecialty.Value
       
        .Offset(0, 47).Value = FrmDataEntry.cmbcadre.Value
       
        .Offset(0, 48).Value = FrmDataEntry.txtadditionalprofessionalskills.Value
       
        .Offset(0, 49).Value = FrmDataEntry.txtadress.Value
       
        .Offset(0, 50).Value = FrmDataEntry.txtwad.Value
       
        .Offset(0, 51).Value = FrmDataEntry.txtlga.Value
       
        .Offset(0, 52).Value = FrmDataEntry.txtsenatorialdst.Value
       
        .Offset(0, 53).Value = FrmDataEntry.cmbstatee.Value
       
        .Offset(0, 54).Value = FrmDataEntry.txtemail.Value
       
        .Offset(0, 55).Value = FrmDataEntry.txttel.Value
       
        .Offset(0, 56).Value = FrmDataEntry.txtmobiletel.Value
       
        .Offset(0, 57).Value = FrmDataEntry.txtaddss.Value
       
        .Offset(0, 58).Value = FrmDataEntry.txtwar.Value
       
        .Offset(0, 59).Value = FrmDataEntry.txtlgga.Value
       
        .Offset(0, 60).Value = FrmDataEntry.txtsendis.Value
       
        .Offset(0, 61).Value = FrmDataEntry.cmbstate.Value
       
        .Offset(0, 62).Value = FrmDataEntry.txtemaill.Value
       
        .Offset(0, 63).Value = FrmDataEntry.txttelll.Value
       
        .Offset(0, 64).Value = FrmDataEntry.txtmobiletelll.Value
       
        .Offset(0, 65).Value = FrmDataEntry.txtsubmittingEntry.Value
       
        .Offset(0, 66).Value = FrmDataEntry.txtoffice.Value
       
        .Offset(0, 67).Value = FrmDataEntry.cmbFacilitytype.Value
       
        .Offset(0, 68).Value = FrmDataEntry.cmblevelofcare.Value
       
        .Offset(0, 69).Value = FrmDataEntry.txtdepartment.Value
       
        .Offset(0, 70).Value = FrmDataEntry.txtunitname.Value
       
        .Offset(0, 71).Value = FrmDataEntry.txtwd.Value
       
        .Offset(0, 72).Value = FrmDataEntry.txtlgaa.Value
       
        .Offset(0, 73).Value = FrmDataEntry.txtdistrictSen.Value
       
        .Offset(0, 74).Value = FrmDataEntry.cmbstattee.Value
       
        .Offset(0, 75).Value = FrmDataEntry.txtsignature.Value
       
        .Offset(0, 76).Value = FrmDataEntry.txtlastdate.Value
               
        .Offset(0, 77).Value = Application.UserName
       
        .Offset(0, 78).Value = Format([Now()], "DD-MM-YYYY HH:MM:SS")
       
    End With
       
    Call Reset_Form
       
    Application.ScreenUpdating = True
   
    MsgBox "Data Submitted Successfully!"
   
End Sub
 
Last edited by a moderator:
Upvote 0
You have posted reply twice? presumably, the original issue resolved but not clear what your additional issue is?

Dave
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Userform to worksheet values followed by 3 digits
Replies
2
Views
185
ipbr21054
I
P
  • Solved
Transferring data between two userforms problem .
Replies
7
Views
292
Pacman52
P
I
  • Solved
Cell above is selected when i duplicate row
Replies
2
Views
85
ipbr21054
I
Sumeluar
  • Solved
Input box with selected text from cell
Replies
8
Views
144
Akuini
Akuini
L
  • Question
Help with VBA code for a userform
Replies
1
Views
250
dmt32
D

Forum statistics

Threads
1,214,952
Messages
6,122,454
Members
449,083
Latest member
Ava19

Share this page

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
Back
Top