Fill multiple tables on different sheets with a single userform

M

Munch91

New Member
Joined
Dec 13, 2020
Messages
4
Office Version
  1. 2013
Platform
  1. Windows
I am very new to VBA, and have created a workbook with a number of sheets and tables that need to be populated with a userform. I have made my userform to have pages that match my workbook - i.e. Cartons Counts, Aisle Hours etc. as these need to be kept separate and are not all needed to be updated at the same time. I chose to add a new command button to each page in the userform that should populate the table on the corresponding sheet in my workbook when clicked. So far this works for the first three worksheets in the workbook from the pages in the userform, but the exact same code causes errors on the last 3 and it doesn't populate the associated tables at all. Every worksheet/table is set up the same way, as are the userform components and they all share a common set of data that is outside of the "page" in the userform (which works on the first 3, but not the last 3). I have checked all of the references are to the correct locations (i.e. the right worksheets and the right textboxes on the user form etc.) and cannot work out why the code is ONLY working for 3 sheets.

I have no idea what is going wrong as everything else seems to work ok in the other parts of the code. There is no special formulas or coding to any of the tables other than what is used when making the table, and renaming them according to the page for identification.

I have included the full code I have so far, and made the sections that are causing trouble bold. They all come up with the following error: "run-time error '91': Object variable or With block variable not set". The first sections of the code, for Transfer Buttons 1-3, the same code all works perfectly fine and the right tables on the right sheets are being populated with the right data from the userform.

Attached also are screenshots (not in XL2BB) of the excel workbook (from a dummy version) and of the userform to give an idea of what I mean by the userform pages corresponding to the workbook pages.

Any help here would be greatly appreciated.

Rich (BB code): 
Private Sub UserForm_Initialize()

DateBox1.SetFocus
LoadTypeCombo.List = Array("Single Sydney", "Single Melbourne", "Double Syd/Melb", "Double Sydney", "Double Melbourne", "Triple", "No NDC", "Other")
DayCombo.List = Array("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday")
ComboBox1.List = Array("Yes", "No", "Partial")
ComboBox2.List = Array("Yes", "No", "Partial")
ComboBox3.List = Array("Yes", "No", "Partial")
ComboBox4.List = Array("Yes", "No", "Partial")
ComboBox5.List = Array("Yes", "No", "Partial")
ComboBox6.List = Array("Yes", "No", "Partial")
ComboBox7.List = Array("Yes", "No", "Partial")
ComboBox8.List = Array("Yes", "No", "Partial")
ComboBox9.List = Array("Yes", "No", "Partial")
ComboBox10.List = Array("Yes", "No", "Partial")
ComboBox11.List = Array("Yes", "No", "Partial")
ComboBox12.List = Array("Yes", "No", "Partial")
ComboBox13.List = Array("Yes", "No", "Partial")
ComboBox14.List = Array("Yes", "No", "Partial")
ComboBox15.List = Array("Yes", "No", "Partial")

End Sub

Private Sub Transfer1_Click()

Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets("Carton Count")

Dim rng As Range
Set rng = myWorksheet.ListObjects("CartonCount").Range
Dim LastRow As Long
LastRow = rng.Find(What:=” * ”, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = DateBox1.Text
    rng.Parent.Cells(LastRow + 1, 2).Value = DayCombo.Text
    rng.Parent.Cells(LastRow + 1, 3).Value = LoadTypeCombo.Text
    rng.Parent.Cells(LastRow + 1, 4).Value = TextBox47.Text
    rng.Parent.Cells(LastRow + 1, 5).Value = TextBox48.Text
    rng.Parent.Cells(LastRow + 1, 6).Value = TextBox49.Text
    rng.Parent.Cells(LastRow + 1, 7).Value = TextBox50.Text
    rng.Parent.Cells(LastRow + 1, 8).Value = TextBox51.Text
    rng.Parent.Cells(LastRow + 1, 9).Value = TextBox52.Text
    rng.Parent.Cells(LastRow + 1, 10).Value = TextBox53.Text
    rng.Parent.Cells(LastRow + 1, 11).Value = TextBox54.Text
    rng.Parent.Cells(LastRow + 1, 12).Value = TextBox55.Text
    rng.Parent.Cells(LastRow + 1, 13).Value = TextBox56.Text
    rng.Parent.Cells(LastRow + 1, 14).Value = TextBox57.Text
    rng.Parent.Cells(LastRow + 1, 15).Value = TextBox58.Text
    rng.Parent.Cells(LastRow + 1, 16).Value = TextBox59.Text
    rng.Parent.Cells(LastRow + 1, 17).Value = TextBox60.Text
    rng.Parent.Cells(LastRow + 1, 18).Value = TextBox61.Text

End Sub

Private Sub Transfer2_Click()

Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets("Aisle Hours")

Dim rng As Range
Set rng = myWorksheet.ListObjects("AisleHours").Range
Dim LastRow As Long
LastRow = rng.Find(What:=” * ”, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = DateBox1.Text
    rng.Parent.Cells(LastRow + 1, 2).Value = DayCombo.Text
    rng.Parent.Cells(LastRow + 1, 3).Value = LoadTypeCombo.Text
    rng.Parent.Cells(LastRow + 1, 4).Value = TextBox16.Text
    rng.Parent.Cells(LastRow + 1, 5).Value = TextBox17.Text
    rng.Parent.Cells(LastRow + 1, 6).Value = TextBox18.Text
    rng.Parent.Cells(LastRow + 1, 7).Value = TextBox19.Text
    rng.Parent.Cells(LastRow + 1, 8).Value = TextBox20.Text
    rng.Parent.Cells(LastRow + 1, 9).Value = TextBox21.Text
    rng.Parent.Cells(LastRow + 1, 10).Value = TextBox22.Text
    rng.Parent.Cells(LastRow + 1, 11).Value = TextBox23.Text
    rng.Parent.Cells(LastRow + 1, 12).Value = TextBox24.Text
    rng.Parent.Cells(LastRow + 1, 13).Value = TextBox25.Text
    rng.Parent.Cells(LastRow + 1, 14).Value = TextBox26.Text
    rng.Parent.Cells(LastRow + 1, 15).Value = TextBox27.Text
    rng.Parent.Cells(LastRow + 1, 16).Value = TextBox28.Text
    rng.Parent.Cells(LastRow + 1, 17).Value = TextBox29.Text
    rng.Parent.Cells(LastRow + 1, 18).Value = TextBox30.Text

End Sub

Private Sub Transfer3_Click()

Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets("Routining")

Dim rng As Range
Set rng = myWorksheet.ListObjects("Routining").Range
Dim LastRow As Long
LastRow = rng.Find(What:=” * ”, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = DateBox1.Text
    rng.Parent.Cells(LastRow + 1, 2).Value = DayCombo.Text
    rng.Parent.Cells(LastRow + 1, 3).Value = LoadTypeCombo.Text
    rng.Parent.Cells(LastRow + 1, 4).Value = ComboBox1.Text
    rng.Parent.Cells(LastRow + 1, 5).Value = ComboBox2.Text
    rng.Parent.Cells(LastRow + 1, 6).Value = ComboBox3.Text
    rng.Parent.Cells(LastRow + 1, 7).Value = ComboBox4.Text
    rng.Parent.Cells(LastRow + 1, 8).Value = ComboBox5.Text
    rng.Parent.Cells(LastRow + 1, 9).Value = ComboBox6.Text
    rng.Parent.Cells(LastRow + 1, 10).Value = ComboBox7.Text
    rng.Parent.Cells(LastRow + 1, 11).Value = ComboBox8.Text
    rng.Parent.Cells(LastRow + 1, 12).Value = ComboBox9.Text
    rng.Parent.Cells(LastRow + 1, 13).Value = ComboBox10.Text
    rng.Parent.Cells(LastRow + 1, 14).Value = ComboBox11.Text
    rng.Parent.Cells(LastRow + 1, 15).Value = ComboBox12.Text
    rng.Parent.Cells(LastRow + 1, 16).Value = ComboBox13.Text
    rng.Parent.Cells(LastRow + 1, 17).Value = ComboBox14.Text
    rng.Parent.Cells(LastRow + 1, 18).Value = ComboBox15.Text

End Sub

Private Sub Transfer4_Click()

Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets("Truck Times")

Dim rng As Range
Set rng = myWorksheet.ListObjects("TruckTimes").Range
Dim LastRow As Long
LastRow = rng.Find(What:=” * ”, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = DateBox1.Text
    rng.Parent.Cells(LastRow + 1, 2).Value = DayCombo.Text
    rng.Parent.Cells(LastRow + 1, 3).Value = LoadTypeCombo.Text
    rng.Parent.Cells(LastRow + 1, 4).Value = TextBox62.Text
    rng.Parent.Cells(LastRow + 1, 5).Value = TextBox63.Text
    rng.Parent.Cells(LastRow + 1, 6).Value = TextBox64.Text
    rng.Parent.Cells(LastRow + 1, 7).Value = TextBox65.Text
    rng.Parent.Cells(LastRow + 1, 8).Value = TextBox66.Text
   
End Sub

Private Sub Transfer5_Click()

Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets("Total Hours")

Dim rng As Range
Set rng = myWorksheet.ListObjects("TotalHours").Range
Dim LastRow As Long
LastRow = rng.Find(What:=” * ”, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = DateBox1.Text
    rng.Parent.Cells(LastRow + 1, 2).Value = DayCombo.Text
    rng.Parent.Cells(LastRow + 1, 3).Value = LoadTypeCombo.Text
    rng.Parent.Cells(LastRow + 1, 4).Value = TextBox67.Text
    rng.Parent.Cells(LastRow + 1, 5).Value = TextBox68.Text
    rng.Parent.Cells(LastRow + 1, 6).Value = TextBox69.Text
    rng.Parent.Cells(LastRow + 1, 7).Value = TextBox70.Text
    rng.Parent.Cells(LastRow + 1, 8).Value = TextBox71.Text
   
End Sub

Private Sub Transfer6_Click()

Dim myWorksheet As Worksheet
Set myWorksheet = Worksheets("Comments")

Dim rng As Range
Set rng = myWorksheet.ListObjects("Comments").Range
Dim LastRow As Long
LastRow = rng.Find(What:=” * ”, _
After:=rng.Cells(1), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

    rng.Parent.Cells(LastRow + 1, 1).Value = DateBox1.Text
    rng.Parent.Cells(LastRow + 1, 2).Value = DayCombo.Text
    rng.Parent.Cells(LastRow + 1, 3).Value = LoadTypeCombo.Text
    rng.Parent.Cells(LastRow + 1, 4).Value = TextBox72.Text
   
End Sub
 

Attachments

  • Userform.PNG
    Userform.PNG
    28.8 KB · Views: 85
  • Workbook.PNG
    Workbook.PNG
    37.1 KB · Views: 86
Last edited by a moderator:

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi & welcome to MrExcel.
Try removing the spaces from either side of the *, also you are using the wrong type of quotes
VBA Code: 
LastRow = rng.Find(What:="*", _
 
Upvote 0
Solution
Hi,
Glancing through your code looks like it may be possible to replace all the repeating code with one common code

As an idea see if following can work for you

MAKE A BACKUP of your workbook & replace ALL you forms code with following

VBA Code: 
Private Sub DateBox1_AfterUpdate()
    If IsDate(DateBox1) Then DayCombo.Text = WeekdayName(Weekday(DateBox1.Text), False, vbSunday)
End Sub


Private Sub UserForm_Initialize()
    Dim i As Integer
   
    For i = 1 To 7
        DayCombo.AddItem WeekdayName(i, False, vbSunday)
    Next i
   
    LoadTypeCombo.List = Array("Single Sydney", "Single Melbourne", "Double Syd/Melb", _
                                "Double Sydney", "Double Melbourne", "Triple", "No NDC", "Other")
   
    For i = 1 To 15
        Me.Controls("ComboBox" & i).List = Array("Yes", "No", "Partial")
    Next i
    DateBox1.SetFocus
End Sub

Private Sub Transfer1_Click()
    AddRecord Me
End Sub

Private Sub Transfer2_Click()
   AddRecord Me
End Sub
Private Sub Transfer3_Click()
    AddRecord Me
End Sub

Private Sub Transfer4_Click()
    AddRecord Me
End Sub

Private Sub Transfer5_Click()
    AddRecord Me
End Sub

Private Sub Transfer6_Click()
    AddRecord Me
End Sub

Place following code either in your forms code page or a standard module

VBA Code: 
Sub AddRecord(ByVal Form As Object)
    Dim myWorksheet As Worksheet
    Dim NewRecord As ListRow
    Dim TableList As ListObject
    Dim ctrl As Control
    Dim c As Integer, PageNo As Integer
    Dim SheetName As String
   
   
    On Error GoTo myerror

    With Form.MultiPage1
        SheetName = .Pages(.Value).Caption
        PageNo = .Value
    End With
   
    Set myWorksheet = Worksheets(SheetName)
    Set TableList = myWorksheet.ListObjects(Replace(SheetName, " ", ""))
    Set NewRecord = TableList.ListRows.Add(AlwaysInsert:=True)
'add new record
    c = 4
    With NewRecord
   
         .Range(1).Value = Form.DateBox1.Text
         .Range(2).Value = Form.DayCombo.Text
         .Range(3).Value = Form.LoadTypeCombo.Text
        
         'loop through each textbox or combobox control on page & post to table
        For Each ctrl In Form.MultiPage1.Pages(PageNo).Controls
            If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            .Range(, c).Value = ctrl.Value
            'optional clear control
            'ctrl.Value = ""
            c = c + 1
            End If
        Next
       
    End With
   
    'inform user
    MsgBox "Record Added To Table " & TableList.Name, 64, "Record Added"
   
myerror:
    'report errors
    If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub

Please understand idea not fully tested and may (or may not) give same results as your original code.

Hope helpful

Dave
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
cut/copy and paste row based on cell value not being equal to specific text
Replies
6
Views
506
mlan1604
M
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
204
ipbr21054
I
I
  • Solved
Textbox on userform not updating when navigating through records
2
Replies
10
Views
476
ipbr21054
I
I
  • Question Question
Advice to move/edit array from module to worksheet
Replies
6
Views
225
ipbr21054
I
P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
481
Pi_Lover
P

Forum statistics

Threads
1,223,952
Messages
6,175,593
Members
452,654
Latest member
mememe101

Share this page

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
Back
Top