using userform to populate Table1 on spreadsheet

JimUSMC

New Member
Joined
Jan 19, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hello everyone,



I have been researching and researching with building userforms and their reactions with the spreadsheets. I came across so many different ways to accomplish the same thing. It's crazy how many people do this differently. So I came out with this fully functioning coding that does the trick.... except when I converted my spreadsheet list to a table (Table1), all hell broke loose.. haha. nothing works. Can anyone help me with altering the code WITHOUT completely rewriting the entire script? And for those who are seeking the same assistance, I will share my current coding and if I can get help with this, I will share the end result as well.

USERFORM:
VBA Code:
Private Sub cmdResetItem_Click()
 Dim msgValue As VbMsgBoxResult
 
 msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Confirmation")
 
 If msgValue = vbNo Then Exit Sub
 
 Call ResetItem
 
End Sub
Private Sub cmdSaveItem_Click()
 
 Dim msgValue As VbMsgBoxResult
 
 msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Confirmation")
 
 If msgValue = vbNo Then Exit Sub
 
 Call SubmitItem
 Call ResetItem
 
End Sub
Private Sub UserForm_Initialize()
 Call ResetItem
End Sub

MODULE:
Code:
    Option Explicit
    Sub ResetItem() ' The reset button coding
     Dim iRow As Long
    
     iRow = [Counta(ItemDatabase! A:A)] 'identify last row
    
     With ItemForm
     .txtItemCode.Value = ""
     .txtItemName.Value = ""
     .txtItemDescription.Value = ""
     .txtSupplierName.Value = ""
    
     .lstItemDatabase.ColumnCount = 5
     .lstItemDatabase.ColumnHeads = True
     .lstItemDatabase.ColumnWidths = "30,60,150,400,45"
    
     If iRow > 1 Then
     .lstItemDatabase.RowSource = "ItemDatabase! A2:E" & iRow
     Else
     .lstItemDatabase.RowSource = "ItemDatabase! A2:E2"
     End If
    
     End With
    End Sub
    Sub SubmitItem()
     Dim sh As Worksheet
     Dim iRow As Long
    
     Set sh = ThisWorkbook.Sheets("ItemDatabase")
    
     iRow = [Counta(ItemDatabase! A:A)] + 1
    
     With sh
    
     .Cells(iRow, 1) = iRow - 1
     .Cells(iRow, 2) = ItemForm.txtItemCode.Value
     .Cells(iRow, 3) = ItemForm.txtItemName.Value
     .Cells(iRow, 4) = ItemForm.txtItemDescription.Value
     .Cells(iRow, 5) = ItemForm.txtSupplierName.Value
    
     End With
    
    End Sub
    Sub Show_Form()
     ItemForm.Show
    End Sub
 

Attachments

  • ItemForm.jpg
    ItemForm.jpg
    95.5 KB · Views: 20

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Instead of these lines
VBA Code:
   Set sh = ThisWorkbook.Sheets("ItemDatabase")
     iRow = [Counta(ItemDatabase! A:A)] + 1
     With sh
     .Cells(iRow, 1) = iRow - 1
     .Cells(iRow, 2) = ItemForm.txtItemCode.Value
     .Cells(iRow, 3) = ItemForm.txtItemName.Value
     .Cells(iRow, 4) = ItemForm.txtItemDescription.Value
     .Cells(iRow, 5) = ItemForm.txtSupplierName.Value
     End With
Try
VBA Code:
    With Sheets("ItemDatabase").ListObjects(1).ListRows.Add
        .Range(1) = .Range.Row - 1
        .Range(2) = ItemForm.txtItemCode.Value
        .Range(3) = ItemForm.txtItemName.Value
        .Range(4) = ItemForm.txtItemDescription.Value
        .Range(5) = ItemForm.txtSupplierName.Value
    End With
 

JimUSMC

New Member
Joined
Jan 19, 2020
Messages
2
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Instead of these lines
VBA Code:
   Set sh = ThisWorkbook.Sheets("ItemDatabase")
     iRow = [Counta(ItemDatabase! A:A)] + 1
     With sh
     .Cells(iRow, 1) = iRow - 1
     .Cells(iRow, 2) = ItemForm.txtItemCode.Value
     .Cells(iRow, 3) = ItemForm.txtItemName.Value
     .Cells(iRow, 4) = ItemForm.txtItemDescription.Value
     .Cells(iRow, 5) = ItemForm.txtSupplierName.Value
     End With
Try
VBA Code:
    With Sheets("ItemDatabase").ListObjects(1).ListRows.Add
        .Range(1) = .Range.Row - 1
        .Range(2) = ItemForm.txtItemCode.Value
        .Range(3) = ItemForm.txtItemName.Value
        .Range(4) = ItemForm.txtItemDescription.Value
        .Range(5) = ItemForm.txtSupplierName.Value
    End With

Yongle, thanks for the effort. made my excel crash. the line (With Sheets("ItemDatabase").ListObjects(1).ListRows.Add) highlighted with yellow telling me it disconnected with client.. not sure what happened.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,977
Office Version
  1. 365
Platform
  1. Windows
Neither do I
The code is standard stuff (using sheet name provided in your code and referring to the first table on that sheet)
I have seen tables corrupted inadvertently due to user actions
- it is safer when users are prevented from making ANY alterations to a table EXCEPT via userform

except when I converted my spreadsheet list to a table (Table1), all hell broke loose.
Perhaps a minor corruption occurred when "all hell broke loose" 😈👹

Is your table an Excel table created with Insert \ Table
- or is there something else going on ?

I suggest you create a NEW test file with a NEW table and NEW userform to test the code
- it works without any issues for me and I am also on 365
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
59,920
Office Version
  1. 365
Platform
  1. Windows
Crossposted using excel userform to populate table1 - OzGrid Free Excel/VBA Help Forum

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

Forum statistics

Threads
1,136,322
Messages
5,675,085
Members
419,549
Latest member
EliteBeat

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