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

Tarkemelion

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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!

VBA Code:
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:

VBA Code:
'Store Company Name from List
Private Sub ListBoxCompany_AfterUpdate()

    Dim CompanyState As String
    
    CompanyState = Me.ListBoxCompany

End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
15,393
Office Version
  1. 2013
Platform
  1. Windows
This code is not necessary, you can comment or delete it.
VBA Code:
''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.

Rich (BB code):
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

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Hi Dante,

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

VBA Code:
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:

VBA 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:

VBA Code:
'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

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
This code is not necessary, you can comment or delete it.
VBA Code:
''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.

Rich (BB code):
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

Well-known Member
Joined
Dec 3, 2018
Messages
15,393
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Jun 28, 2022
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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!!!


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

Well-known Member
Joined
Dec 3, 2018
Messages
15,393
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Solution

Forum statistics

Threads
1,181,670
Messages
5,931,337
Members
436,787
Latest member
ogharipour

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top