VBA: Help with Adding a Row to a Table containing Userform Information

[Tarkemelion]

Hi everyone,

I have a Userform where information is entered by the user. Once they click the cmdAdd button, the code should create a new row in the table and then copy the user added information into the relevant columns. When adding ListBoxes to my Userform, my code no longer works as intended (Excel crashes) and I'm wondering if I need to treat ListBox information differently from TextBox information. Have a look at my code and let me know where I'm going wrong.

Cheers!

Private Sub cmdAdd_Click()

'Defines the variables
    Dim Description As String
    Dim Docket_Number As String
    
    Dim Rate As String
    Dim Quantity As String
    Dim Unit As String
    Dim Addition As String
    
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Cost Detail")
    
    Dim tbl As ListObject
    Set tbl = ws.ListObjects("Table1")
    
'Sets variables to UserForm values
    Description = TextBoxDescription.Text
    Docket_Number = TextBoxDocket.Text
    
    Rate = TextBoxRate.Text
    Quantity = TextBoxQuantity.Text
    Unit = TextBoxUnit.Text
    Addition = TextBoxAddition.Text
    
    Dim NewRow As ListRow
    Set NewRow = tbl.ListRows.Add
    
    With NewRow
    
        .Range(1) = ""
        .Range(2) = ""
        .Range(3) = ""
        .Range(4) = ShiftState
        .Range(5) = CompanyState
        .Range(6) = Description
        .Range(7) = Docket_Number
        .Range(8) = CodeState
        .Range(9) = Rate
        .Range(10) = Quantity
        .Range(11) = Unit
        .Range(12) = Addition
        
    End With

'Closes UserForm
    Unload Me
    
End Sub

The items with ListBoxes are "ShiftState", "CompanyState" and "CodeState". I have additional code that is trying to capture the ListBox variable for each one as follows:

'Store Company Name from List
Private Sub ListBoxCompany_AfterUpdate()

    Dim CompanyState As String
    
    CompanyState = Me.ListBoxCompany

End Sub

 

[DanteAmor]

This code is not necessary, you can comment or delete it.

''Store Company Name from List
'Private Sub ListBoxCompany_AfterUpdate()
'    Dim CompanyState As String
'    CompanyState = Me.ListBoxCompany
'End Sub

I put a couple of examples for company and shift, just adjust the name of the controls.

Private Sub cmdAdd_Click()
'Defines the variables
    Dim CompanyState As String, ShiftState As String, CodeState As String
   
    Dim Description As String
    Dim Docket_Number As String
    Dim Rate As String
    Dim Quantity As String
    Dim Unit As String
    Dim Addition As String
   
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim NewRow As ListRow
   
'Validate data
    If ListBoxCompany.ListIndex = -1 Then
      MsgBox "Select a company"
      Exit Sub
    End If
    If ListBoxShiftState.ListIndex = -1 Then
      MsgBox "Select a ShiftState"
      Exit Sub
    End If
   
'Sets variables to UserForm values
    Set ws = ThisWorkbook.Worksheets("Cost Detail")
    Set tbl = ws.ListObjects("Table1")
    Set NewRow = tbl.ListRows.Add
   
    Description = TextBoxDescription.Text
    Docket_Number = TextBoxDocket.Text
    Rate = TextBoxRate.Text
    Quantity = TextBoxQuantity.Text
    Unit = TextBoxUnit.Text
    Addition = TextBoxAddition.Text
   
    CompanyState = ListBoxCompany
    ShiftState = ListBoxShiftState
   
    With NewRow
        .Range(1) = ""
        .Range(2) = ""
        .Range(3) = ""
        .Range(4) = ShiftState
        .Range(5) = CompanyState
        .Range(6) = Description
        .Range(7) = Docket_Number
        .Range(8) = CodeState
        .Range(9) = Rate
        .Range(10) = Quantity
        .Range(11) = Unit
        .Range(12) = Addition
    End With

'Closes UserForm
    Unload Me
End Sub

 

[Tarkemelion]

Hi Dante,

Thanks for your reply, I've implemented the following using your examples:

Private Sub cmdAdd_Click()

'Defines the variables
    Dim CompanyState As String
    Dim Description As String
    Dim Docket_Number As String
    
    Dim ShiftState As String
    
    Dim CodeState As String
    Dim Rate As String
    Dim Quantity As String
    Dim Unit As String
    Dim Addition As String
    
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim NewRow As ListRow
    
'Validate the data
    If ListBoxCompany.ListIndex = -1 Then
        MsgBox "Please select a Company"
        Exit Sub
    End If
    
    If ListBoxShift.ListIndex = -1 Then
        MsgBox "Please nominate a shift"
        Exit Sub
    End If
    
    If ListBoxCode.ListIndex = -1 Then
        MsgBox "Please select a code"
        Exit Sub
    End If
    
'Sets variables to UserForm values
    Set ws = ThisWorkbook.Worksheets("Cost Detail")
    Set tbl = ws.ListObjects("Table1")
    Set NewRow = tbl.ListRows.Add

    CompanyState = ListBoxCompany
    Description = TextBoxDescription.Text
    Docket_Number = TextBoxDocket.Text
    
    ShiftState = ListBoxShift
    
    CodeState = ListBoxCode
    Rate = TextBoxRate.Text
    Quantity = TextBoxQuantity.Text
    Unit = TextBoxUnit.Text
    Addition = TextBoxAddition.Text
            
    With NewRow
    
        .Range(1) = ""
        .Range(2) = ""
        .Range(3) = ""
        .Range(4) = ShiftState
        .Range(5) = CompanyState
        .Range(6) = Description
        .Range(7) = Docket_Number
        .Range(8) = CodeState
        .Range(9) = Rate
        .Range(10) = Quantity
        .Range(11) = Unit
        .Range(12) = Addition
        
    End With

'Closes UserForm
    Unload Me
    
End Sub

There are a few issues which you may want to problem solve with me. Any feedback is always appreciated.

Firstly, the following validation code:

'Validate the data
    If ListBoxCompany.ListIndex = -1 Then
        MsgBox "Please select a Company"
        Exit Sub
    End If

Although the MsgBox is a nice touch, it means the user has to select an option from the ListBox and then hit enter to confirm which is a little annoying. If there is any way around this that would be helpful.

The larger issue is that the code doesn't execute. I looked at it step by step and the problem occurs here:

'Sets variables to UserForm values
    Set ws = ThisWorkbook.Worksheets("Cost Detail")
    Set tbl = ws.ListObjects("Table1")
    Set NewRow = tbl.ListRows.Add

    CompanyState = ListBoxCompany      ' <-- This is the issue
    Description = TextBoxDescription.Text
    Docket_Number = TextBoxDocket.Text

The specific dialog problem is:

Runtime Error:
"Method: 'Add' of Object ListRow failed"

It looks like the code doesn't like taking the ListBox value and storing it as a string.

Many thanks for the help!

 

[Tarkemelion]

This code is not necessary, you can comment or delete it.

''Store Company Name from List
'Private Sub ListBoxCompany_AfterUpdate()
'    Dim CompanyState As String
'    CompanyState = Me.ListBoxCompany
'End Sub

I put a couple of examples for company and shift, just adjust the name of the controls.

Private Sub cmdAdd_Click()
'Defines the variables
    Dim CompanyState As String, ShiftState As String, CodeState As String
   
    Dim Description As String
    Dim Docket_Number As String
    Dim Rate As String
    Dim Quantity As String
    Dim Unit As String
    Dim Addition As String
  
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim NewRow As ListRow
  
'Validate data
    If ListBoxCompany.ListIndex = -1 Then
      MsgBox "Select a company"
      Exit Sub
    End If
    If ListBoxShiftState.ListIndex = -1 Then
      MsgBox "Select a ShiftState"
      Exit Sub
    End If
  
'Sets variables to UserForm values
    Set ws = ThisWorkbook.Worksheets("Cost Detail")
    Set tbl = ws.ListObjects("Table1")
    Set NewRow = tbl.ListRows.Add
  
    Description = TextBoxDescription.Text
    Docket_Number = TextBoxDocket.Text
    Rate = TextBoxRate.Text
    Quantity = TextBoxQuantity.Text
    Unit = TextBoxUnit.Text
    Addition = TextBoxAddition.Text
  
    CompanyState = ListBoxCompany
    ShiftState = ListBoxShiftState
  
    With NewRow
        .Range(1) = ""
        .Range(2) = ""
        .Range(3) = ""
        .Range(4) = ShiftState
        .Range(5) = CompanyState
        .Range(6) = Description
        .Range(7) = Docket_Number
        .Range(8) = CodeState
        .Range(9) = Rate
        .Range(10) = Quantity
        .Range(11) = Unit
        .Range(12) = Addition
    End With

'Closes UserForm
    Unload Me
End Sub

Hi Dante,

As per my above response, it appears the below line is the issue:

Set NewRow = tblListRows.Add

I have no idea why this is breaking the code. Re-organising and stepping through everything shows all the variable are being saved ready to be implemented but as soon as the new row tries to add the spreadsheet crashes. Any thoughts?

 

[DanteAmor]

Set NewRow = tblListRows.Add

you are missing a dot
Set NewRow = tbl.ListRows.Add

My tests work. I need to test with your data and see what changes.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

You explain to me step by step what you do to get to the error.

 

[Tarkemelion]

you are missing a dot
Set NewRow = tbl.ListRows.Add

My tests work. I need to test with your data and see what changes.

You could upload a copy of your file to a free site such www.dropbox.com or google drive. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

You explain to me step by step what you do to get to the error.

Hi Dante,

Sorry about the delayed response. I do very much appreciate your help. I managed to step through the current code and it did add the row and all the variables before crashing right at the very end!!!

Monthly Costing Schedule Rev02 Sanitised.xlsm

Shared with Dropbox

www.dropbox.com

Have a go seeing if you can find what is going wrong with my spreadsheet, it should be effectively same but it is a blank template

 

[DanteAmor]

I guess your sheet has a fault. You will need to copy your sheets to another book, copy your form and macros to new book. You should no longer have problems.