How to solve unpredictable error: "method of object range/worksheet failed

[phillipus2005]

Dear Gurus

I am having a real problem with this. i have created a spreadsheet to log "projects" and various details about each one, e.g. country, lead staff member, date opened etc. I have roughly 40 columns that need to be populated.

I have created a userform, called FrmNewCase, with all the fields to populate, using a mixture of text boxes and comboboxes, with named ranges as source.

It worked fine for a while then (either at random or because i did something wrong), i started getting an error: "Method of object range failed". clicking debug, highlighted this line of code: ".Range("A" & rw) = TxtCaseName" but then Excel crashed. Every time. i then modified the code to Sheets("Live cases Input").Range("A" & rw) and now i sometimes get a similar error: "method of object worksheet failed, the original error, or none at all, seemingly at random...

a few points to note - when i get the "worksheets failed error", Excel does not crash, puts the case name in the correct field but does not populate any others. when i get the "range failed" error, nothing populates.

I really cannot figure out what's going on here and would really welcome any advice or guidance on how to fix this... any advice on how to do this in a more efficient way also extremely welcome...

Thanks in advance

Private Sub CmdAddDD_Click()


    FrmDD.Show ' a different form called when clicking this button
    
End Sub


Private Sub CmdClearForm_Click()


    ClearForm 'calls the ClearForm subroutine
    
End Sub




 Private Sub Userform_Activate()


'With Me
        'This will create a vertical scrollbar
'        .ScrollBars = fmScrollBarsVertical
        
        'Change the values of 2 as Per your requirements
'        .ScrollHeight = .InsideHeight * 2
'        .ScrollWidth = .InsideWidth * 9
'    End With
    
'Load case info tab on initialising form
'    MultiPage1.Value = 0 'Displays the first tab. 'multitab not used
    
'put cursor in case name text box
    TxtCaseName.SetFocus
    
'Set the row sources for the comboboxes
    CmbType.RowSource = "RngType" 'set range for Type combobox
    CmbPrimaryLocation.RowSource = "RngLocations" 'set range for Primary Locations combobox
    CmbPSSS.RowSource = "RngYesNo" 'set range for yes/no combobox
    CmbHNWI.RowSource = "RngYesNo"
    CmbPF.RowSource = "RngYesNo"
    CmbSLB.RowSource = "RngYesNo"
    CmbCountry.RowSource = "RngLocations"
    CmbLNWC.RowSource = "RngYesNo"
    CmbDDQ.RowSource = "RngYesNo"
    CmbPSR.RowSource = "RngYesNo"
    CmbLNWCFlags.RowSource = "RngYesNo"
    CmbDDQFlags.RowSource = "RngYesNo"
    CmbPSRFlags.RowSource = "RngYesNo"
    
    
'set default text for date text box
    TxtDate.Value = "DD/MM/YY"


End Sub


Private Sub TxtDate_GotFocus()
    TxtDate.Text = ""


End Sub


Private Sub CmdEnterNewCase_Click()


Dim rw As Long    'next available row


'clear report generation cell in Report template worksheet
    Worksheets("Report_template").Range("B2").Value = ""


With Sheets("Live cases Input")
'get the next avialable row in Sheet1, call it "rw"
    rw = (Sheets("Live cases Input").Range("A" & .Rows.Count).End(xlUp).Row + 1)
 
'put the text box values in this row
    Sheets("Live cases Input").Range("A" & rw) = TxtCaseName 
    Sheets("Live cases Input").Range("B" & rw) = TxtDate 
    Sheets("Live cases Input").Range("B" & rw) = CDate(Me.TxtDate.Value) 'formats the case open date as a date
    Sheets("Live cases Input").Range("C" & rw) = CmbType 
    Sheets("Live cases Input").Range("D" & rw) = CmbPrimaryLocation.Text 
    Sheets("Live cases Input").Range("F" & rw) = TxtPractice 'column E is a formula 
    Sheets("Live cases Input").Range("G" & rw) = TxtLeadPartner 
    Sheets("Live cases Input").Range("H" & rw) = TxtFFLead 
    Sheets("Live cases Input").Range("L" & rw) = CmbPSSS
    Sheets("Live cases Input").Range("M" & rw) = CmbHNWI
    Sheets("Live cases Input").Range("N" & rw) = CmbPF
    Sheets("Live cases Input").Range("O" & rw) = CmbSLB
    Sheets("Live cases Input").Range("J" & rw) = TxtRelatedClient
    Sheets("Live cases Input").Range("AL" & rw) = TxtAddress1
    Sheets("Live cases Input").Range("AM" & rw) = TxtAddress2
    Sheets("Live cases Input").Range("AN" & rw) = TxtCity
    Sheets("Live cases Input").Range("AO" & rw) = TxtState
    Sheets("Live cases Input").Range("AP" & rw) = TxtPostCode
    Sheets("Live cases Input").Range("AQ" & rw) = CmbCountry
    Sheets("Live cases Input").Range("AK" & rw) = TxtDN
    Sheets("Live cases Input").Range("AR" & rw) = TxtWebsite
    Sheets("Live cases Input").Range("AS" & rw) = TxtKeyExecutives1
    Sheets("Live cases Input").Range("AT" & rw) = TxtKeyExecutives2
    Sheets("Live cases Input").Range("AU" & rw) = TxtKeyExecutives3
    Sheets("Live cases Input").Range("AV" & rw) = TxtKeyExecutives4
    Sheets("Live cases Input").Range("AW" & rw) = TxtKeyExecutives5
    Sheets("Live cases Input").Range("AE" & rw) = TxtDate & " - " & TxtSummary
    Sheets("Live cases Input").Range("Q" & rw) = CmbLNWC
    Sheets("Live cases Input").Range("R" & rw) = CmbLNWCFlags
    Sheets("Live cases Input").Range("S" & rw) = TxtLNWCSummary
    Sheets("Live cases Input").Range("T" & rw) = CmbDDQ
    Sheets("Live cases Input").Range("U" & rw) = CmbDDQFlags
    Sheets("Live cases Input").Range("V" & rw) = TxtDDQFlagSummary
    Sheets("Live cases Input").Range("W" & rw) = CmbPSR
    Sheets("Live cases Input").Range("X" & rw) = CmbPSRFlags
    Sheets("Live cases Input").Range("Y" & rw) = TxtPSRFlags


    Application.CutCopyMode = False
    
End With
 
'calls the clearform subroutine
    ClearForm
        
'Go to Live Cases Input worksheet
'    Worksheets("Live Cases Input").Activate
    
EndRoutine:


'Optimize Code
  Application.ScreenUpdating = True
  Application.EnableEvents = True


'Clear The Clipboard
  Application.CutCopyMode = False
  
'Close the userform
    FrmNewCase.Hide


End Sub


Sub ClearForm()
'clear the text boxes
    TxtCaseName.Value = ""
    TxtDate.Value = ""
    CmbType.Value = ""
    CmbPrimaryLocation.Value = ""
    TxtPractice.Value = ""
    TxtLeadPartner.Value = ""
    TxtFFLead.Value = ""
    CmbPSSS.Value = ""
    CmbHNWI.Value = ""
    CmbPF.Value = ""
    CmbSLB.Value = ""
    TxtRelatedClient.Value = ""
    TxtAddress1.Value = ""
    TxtAddress2.Value = ""
    TxtCity.Value = ""
    TxtState.Value = ""
    TxtPostCode.Value = ""
    CmbCountry.Value = ""
    TxtDN.Value = ""
    TxtWebsite.Value = ""
    TxtKeyExecutives1.Value = ""
    TxtKeyExecutives2.Value = ""
    TxtKeyExecutives3.Value = ""
    TxtKeyExecutives4.Value = ""
    TxtKeyExecutives5.Value = ""
    TxtDDSummary.Value = ""
    CmbLNWC.Value = ""
    CmbDDQ.Value = ""
    CmbPSR.Value = ""
    TxtLNWCSummary.Value = ""
    TxtDDQFlagSummary.Value = ""
    TxtPSRFlags.Value = ""
    CmbLNWCFlags.Value = ""
    CmbDDQFlags.Value = ""
    CmbPSRFlags.Value = ""


End Sub

 

[phillipus2005]

Oh and no it’s not protected ?

 

[RoryA]

Assuming there is only one table on the sheet, either way should work. Is the table filtered currently, or is there anything directly below it?

 

[phillipus2005]

I wondered if there may be text below but checked that and no... also had removed all filters...

 

[RoryA]

Can you put the file somewhere (eg OneDrive, Dropbox) for me to have a look at?

 

[phillipus2005]

Thank you very much for the offer...Will take me a short while to sanitise. Hang tight.

 

[RoryA]

Also can you let me know which version of Office you are using?

 

[phillipus2005]

Sorry - going to take longer than anticipated to sanitise... will try and PM it to you over the weekend... thanks for your help. very very much appreciated

 

[phillipus2005]

Office 365 proplus