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

 

[RoryA]

Welcome to the forum.

I would strongly advise against using the Rowsource property to populate controls, especially if the named ranges are dynamic. I'd recommend using the List property instead like this:

CmbType.List = Application.Range("RngType").Value

 

[phillipus2005]

Thanks rory for the advice. The names ranges are not dynamic but will follow guidance anyway...
Is that what’s causing the errors??

One other point to note is that the table where I’m trying to populate text is an excel “table” (rather than a range). Would that make a difference to anything?

 

[RoryA]

I can't say for sure if that's what is causing your problem, but I'd recommend changing the code anyway.

Tables can be a little odd in their behaviour. You might find it better to actually address the table as a table and use its ListRows.Add method to add a new row and then populate it.

 

[phillipus2005]

That sounds like a fantastic idea... how would I do that? Happy to explore google but if you had any tips, would really appreciate...

 

[RoryA]

Something like this:

Dim ws As Worksheet
Dim lo As ListObject
Dim newrow As ListRow

Set ws = ActiveSheet

Set lo = ws.ListObjects(1)

Set newrow = lo.ListRows.Add
With newrow
    .Range(1) = TxtCaseName 
    .Range(2) = CDate(Me.TxtDate.Value)
    .Range(3) = CmbType 
End With

Note that the Range addressing is just a column number.

 

[phillipus2005]

Superstar. Thank you. Will give it a go, and report back if I’m still having error issues

 

[phillipus2005]

so i get a "method add of object listrows failed" error the excel crash
I also got some errors "permission denied" on some of the combobox lists - set up per your earlier post...

 

[RoryA]

You need to clear out any Rowsource that has been set for the comboboxes, either at design time or in the code. That should fix the permission denied problem.

Your worksheet isn't protected is it?

 

[phillipus2005]

Thanks. That appears to have solved the permissions error but just got the “method of object listrows failed....

Stupid question: in the “set lo...” line, I’ve to specified my table name in the brackets, replacing “1”)... am I being dim? Though I got the same error behaviour leaving as (1) too