OK, here goes.
I've added some comments in the code, as I'm trying to interpret what your code is up to.
VBA Code:
Private Sub CommandButton1_Click()
Dim wks As Worksheet
Dim addnew As Range
Dim f As Range
Dim r As Range
Dim tbl As ListObject, tRow As ListRow, tCol As ListColumn
Dim ctrl As Control
Set wks = Sheet1
Set tbl = wks.ListObjects("tbl_PublishHouses") 'or set tbl=wks.ListObjects(1)
'FBC - Get a new row into the addnew object, if there are no data rows, the blank row is the "InsertRowRange".
'FBC - If there are data rows, these can be referenced as ListRows, and a new one can be added to the table with the .Add method.
'FBC - if there are data rows, check for the Website/Publishing house name already existing
If Not tbl.InsertRowRange Is Nothing Then
Set addnew = tbl.InsertRowRange
Else
Set tCol = tbl.ListColumns("Website")
Set f = tCol.DataBodyRange.Find(TextBox2.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
MsgBox "Website Name Already exists: " & TextBox2.Value
Exit Sub
End If
Set tCol = tbl.ListColumns("Name")
Set f = tCol.DataBodyRange.Find(TextBox1.Value, , xlValues, xlWhole)
If Not f Is Nothing Then
MsgBox "Publisher House Name Already exists: " & TextBox1.Value
Exit Sub
End If
Set tRow = tbl.ListRows.Add
Set addnew = tRow.Range
End If
'FBC - loop through controls on form looking for textboxes
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox" Then
'FBC - use the number in the textbox name to identify the column in the row, and set the value in that column to the value in the textbox
addnew.Cells(1, Val(Right(ctrl.Name, Len(ctrl.Name) - Len("TextBox")))).Value = ctrl.Value
'ctrl.Value = "" ' Clear the value in the control
End If
Next ctrl
'clear the values in the userform
For x = 1 To cNum
'FBC - there was no code in here. the commented code in the loop above will clear the value of the textbox after adding it to the table in the worksheet
Next
'communicate the results
'FBC - you reference an variable/object "sht" here, but this is not declared or set in advance - I'm not clear what this is supposed to be.
MsgBox "The values have been sent to the " & sht & " Publisher House Sheet"
Dim arrItems()
Dim cnt As Long
Dim pro As Long
'You now reference "TextBox7", but use a "ListCount" property that doesn't belong to a textbox, so I assume this is actually a ListBox or a ComboBox...
'You appear to run a loop to add items to an array if they are selected, so I'm going to assume this is actually a listbox with multi-select turned on
For pro = 0 To TextBox7.ListCount - 1
If TextBox7.Selected(pro) Then
ReDim Preserve arrItems(cnt)
arrItems(cnt) = TextBox7.List(pro)
cnt = cnt + 1
End If
Next pro
If cnt > 0 Then
addnew.Cells(1, 7).Value = Join(arrItems, "|")
End If
Dim arrItems1()
Dim op As Long
Dim om As Long
'As Above, but now "TextBox6" appears to be a Listbox
For om = 0 To TextBox6.ListCount - 1
If TextBox6.Selected(om) Then
ReDim Preserve arrItems1(op)
arrItems1(op) = TextBox6.List(om)
op = op + 1
End If
Next om
If op > 0 Then
addnew.Cells(1, 6) = Join(arrItems1, "|")
End If
End Sub
I'd recommend that you spend a little time naming the objects (textboxes, listboxes etc) in a more useful way.
I'd name each object after the column in the target table that the data is going to be input into.
You can then use that to reference the objects/columns. Then, should you find you're adding new columns/objects, you don't have to worry about the order, or whether it'll break the assumption that the number in the textbox name relates to the number of the column in the table.
For example, the loop where I iterate through the Controls to pick out the textboxes and put their values into the table could be re-written like this;
VBA Code:
'FBC - loop through controls on form looking for textboxes
For Each ctrl In Me.Controls
If TypeName(ctrl) = "TextBox" Then
'FBC - Match the textbox name to a column name in the target table and add the textbox value into that column
Set f = tbl.HeaderRowRange.Find(ctrl.Name, , xlValues, xlWhole)
If Not f Is Nothing Then
Set tCol = tbl.ListColumns(ctrl.Name)
addnew.Cells(1, tCol.Index).Value = ctrl.Value
End If
ctrl.Value = ""
End If
Next ctrl
and that would allow columns to be moved, added etc.
You'd have to have column names without spaces in your table (or replace the spaces with underscore and adapt the code to reflect that), but these are all essentially best practice things anyway.
some of my information goes on a row and others go to another row
