Compile error: Procedure too large - need help reducing code

[VLange]

Hello -

My code won't run now that I've added all my fields to transfer from my user form to my Excel worksheet. I'm new to VBA so I know I went about this the long way, so if anyone can help me reduce the lines that would be great. Here is the first part of the code that adds a new customer to the Excel worksheet. I'll need to have code to pull the data from the worksheet back into the user form but I can't do that until I get the data into the worksheet.

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


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


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


'copy the data to the database
ws.Cells(iRow, 2).Value = Me.cboCustomer.Value
ws.Cells(iRow, 3).Value = Me.txtAcctExec.Value


If ckbTer1.Value = True Then
  ws.Cells(iRow, 4).Value = "X"
 Else
   ws.Cells(iRow, 4).Value = ""
End If


If ckbTer2.Value = True Then
  ws.Cells(iRow, 5).Value = "X"
 Else
   ws.Cells(iRow, 5).Value = ""
End If


If ckbTer2IL.Value = True Then
  ws.Cells(iRow, 6).Value = "X"
 Else
   ws.Cells(iRow, 6).Value = ""
End If


If ckbTer3.Value = True Then
  ws.Cells(iRow, 7).Value = "X"
 Else
   ws.Cells(iRow, 7).Value = ""
End If


If ckbTer4.Value = True Then
  ws.Cells(iRow, 8).Value = "X"
 Else
   ws.Cells(iRow, 8).Value = ""
End If


ws.Cells(iRow, 9).Value = Me.txtCompanyName.Value
ws.Cells(iRow, 10).Value = Me.txtAddress1.Value
ws.Cells(iRow, 11).Value = Me.txtAddress2.Value
ws.Cells(iRow, 12).Value = Me.txtCity.Value
ws.Cells(iRow, 13).Value = Me.txtState.Value
ws.Cells(iRow, 14).Value = Me.txtZip.Value
ws.Cells(iRow, 15).Value = Me.txtPhone.Value
ws.Cells(iRow, 16).Value = Me.txtWebsite.Value


 If ckbAuto.Value = True Then
  ws.Cells(iRow, 17).Value = "X"
 Else
   ws.Cells(iRow, 17).Value = ""
End If


 If ckbHome.Value = True Then
  ws.Cells(iRow, 18).Value = "X"
 Else
   ws.Cells(iRow, 18).Value = ""
End If


 If ckbLife.Value = True Then
  ws.Cells(iRow, 19).Value = "X"
 Else
   ws.Cells(iRow, 19).Value = ""
End If


 If ckbCommercial.Value = True Then
  ws.Cells(iRow, 20).Value = "X"
 Else
   ws.Cells(iRow, 20).Value = ""
End If


 If ckbAntiqueCars.Value = True Then
  ws.Cells(iRow, 21).Value = "X"
 Else
   ws.Cells(iRow, 21).Value = ""
End If


 If ckbAutoWarranties.Value = True Then
  ws.Cells(iRow, 22).Value = "X"
 Else
   ws.Cells(iRow, 22).Value = ""
End If


 If ckbMotorcycles.Value = True Then
  ws.Cells(iRow, 23).Value = "X"
 Else
   ws.Cells(iRow, 23).Value = ""
End If


 If ckbHighIncome.Value = True Then
  ws.Cells(iRow, 24).Value = "X"
 Else
   ws.Cells(iRow, 24).Value = ""
End If


 If ckbStandard.Value = True Then
  ws.Cells(iRow, 25).Value = "X"
 Else
   ws.Cells(iRow, 25).Value = ""
End If


 If ckbNonStandard.Value = True Then
  ws.Cells(iRow, 26).Value = "X"
 Else
   ws.Cells(iRow, 26).Value = ""
End If


 If ckbReinsurance.Value = True Then
  ws.Cells(iRow, 27).Value = "X"
 Else
   ws.Cells(iRow, 27).Value = ""
End If


ws.Cells(iRow, 28).Value = Me.txtNumEEs.Value
ws.Cells(iRow, 29).Value = Me.txtPolicySystem.Value
ws.Cells(iRow, 30).Value = Me.txtClaimsSystem.Value
ws.Cells(iRow, 31).Value = Me.txtBillingSystem.Value
ws.Cells(iRow, 32).Value = Me.txtParentCo.Value
ws.Cells(iRow, 33).Value = Me.txtAffliatesSubs.Value


  If ckbNDA.Value = True Then
  ws.Cells(iRow, 34).Value = "X"
 Else
   ws.Cells(iRow, 34).Value = ""
End If


ws.Cells(iRow, 35).Value = Me.txtNDAExpireDte.Value


  If ckbMSA.Value = True Then
  ws.Cells(iRow, 36).Value = "X"
 Else
   ws.Cells(iRow, 36).Value = ""
End If


ws.Cells(iRow, 37).Value = Me.txtMSAExpireDte.Value
ws.Cells(iRow, 38).Value = Me.txtTotalPIF.Value
ws.Cells(iRow, 39).Value = Me.txtTotalNumAgents.Value
ws.Cells(iRow, 40).Value = Me.txtTotalVehInsured.Value
ws.Cells(iRow, 41).Value = Me.txtTotalStatesWrtngPerAuto.Value
ws.Cells(iRow, 42).Value = Me.txtTotalNewAutoPolSoldMos.Value
ws.Cells(iRow, 43).Value = Me.txtRetentionRatePerc.Value
ws.Cells(iRow, 44).Value = Me.txtNumAgentsWhoCanSellUBI.Value
ws.Cells(iRow, 45).Value = Me.txtTotalVehInsuredByUBI.Value
ws.Cells(iRow, 46).Value = Me.txtPercTotalVehInsByUBI.Value

Thank you!
Valerie

 

[mole999]

if you split out the vba into something like this, that should help

sub master
macro1
macro2
end sub

sub macro1
first 500 lines of code
end sub

sub macro2
second 500 lines of code
end sub

or variants on that

 

[dmt32]

Hi,
suggestion made by Mole999 is most likely way you need to go but it would be helpful if you could place copy of your workbook in a dropbox & supply a link to it here. maybe then be able to give further guidance to reducing size of your code.


Dave.