0

I am trying to create a UserForm to track Job applications. Once user fills in the textboxes and click save, it transfers data from textboxes to excel sheet named "Database". The following code is my Sub Save which runs inside Save_CmdBt_Click event. When I enter my first data record and click save, everything works fine - no error. However, when I enter second data record, I get Method '_Default' of object 'Range' failed error. While debugging, it fails at .Cells(iRow, 1) = iRow - 5 line in code.

    Dim sh As Worksheet
    Dim iRow As Long
       
    Set sh = ThisWorkbook.Sheets("Database")                'actual database sheet
    
    If UserFormV2.RowNumber_Textbox.Value = "" Then         'when I debug for 2nd record
        iRow = [Counta(Database!A:A)] + 1                   'this line of code was executed, thus, txtbox value was "" for sure
    Else
        iRow = UserFormV2.RowNumber_Textbox.Value           'set to "" unless user selects one entry from listbox
    End If
    
    With sh
        '.Activate
        .Cells(iRow, 1) = iRow - 5 '-5 because that is first available row after Table header. 
        ' The above code adds record number (1,2,3...) in excel table. 

        '.Cells(iRow, 3) = UserFormV2.CompanyName_TextBox1.Value ... my extra code
        
    End With

I have check the following post (VBA Error Method of '_Default' if object 'range' failed when inserting into a table) but I do not have any textbox when the error triggers. Any idea or suggest how I can resolve this issue?

Edit 2 - Added Images of Excel sheet and comparison between working and non-working sub save method.

Edit 3 - Images removed. Error is in Listbox which gets updated after every save/delete calls.

Edit 4 (Solution to Problem)

If and Else statement regarding the ListBox1 was the cause of the error. Essentially, I wanted to have a dynamic ListBox and initially had conditions that only applies to second row in the table (which is the header and first row). Thus, after 2nd record/data, the ListBox would not update since it cannot due to set conditions thus resulting Method '_default' of Object 'Range' failed' error.

SpaceEngineer007
  • 1
  • 3
  • I tried testing your code and wasn't able to reproduce the error with the first entry starting in row 5, can you add some sample data from your `"Database"` sheet? I also recomend [this post about finding the last used row](https://stackoverflow.com/questions/38882321/better-way-to-find-last-used-row) as the `Counta` method can be unreliable (such as if there is a blank cell in your data). – Samuel Everson Apr 19 '21 at 13:35
  • I tried replacing the Counta with End(xlUp) method to find last used row and still got the same error. What is really interesting is that I have original version of this code (this is V2), and version 1 works perfectly without any errors. I compared both Sub Save (v1 verus v2) and both are identical. I'll add the sample data from "Database" later today. – SpaceEngineer007 Apr 19 '21 at 14:52
  • What is the value of `iRow` when you get the error? (To find out, `Debug.Print "iRow =", iRow` just before the line that errors. You'll see the value in the Immediate Window) – chris neilsen Apr 19 '21 at 23:49
  • @chrisneilsen So I debug my code one section at time and my issue was with If Else statement for Listbox RowSource. – SpaceEngineer007 Apr 20 '21 at 00:10

1 Answers1

0

Ensure iRow is a valid number

Private Sub CommandButton1_Click()
    Dim sh As Worksheet, iRow As Long
    Set sh = ThisWorkbook.Sheets("Database") 'actual database sheet
    
    If IsNumeric(RowNumber_TextBox.Value) Then iRow = RowNumber_TextBox.Value
    If iRow < 1 Then
       iRow = sh.Cells(Rows.Count, "A").End(xlUp).Row + 1
    End If
    
End Sub

Edit - IsNumeric added

CDP1802
  • 13,871
  • 2
  • 7
  • 17
  • Since `RowNumber_Textbox.Value` is set empty initially, this causes a problem since iRow is define as Long. Form can successful enter **FIRST** record/data from textboxes to excel without any errors. It throws error on second time when I click "Save" button. – SpaceEngineer007 Apr 19 '21 at 23:13
  • @SpaceEngineer Yes, I forgot VB doesn't convert empty strings to 0 like some other languages - code updated. Interestingly I found while testing that `[Counta(Database!A:A)]` returns 1 even when there is no sheet named Database. That surprised me. – CDP1802 Apr 20 '21 at 11:26