Is there a way to select a table based on a userform input?

Question

I created a universal userform on which the user would select a building location from a combobox. This is tied to a sheet with the same name as the location. I am looking to have it so that when they hit the enter button, the data on the form will be entered in a table on the corresponding page. Because Tables have to have a unique name that is not repeated across any sheets, I am struggling with how to set the table range using a string. I named all tables with the following format: TableSheetname. Is there a way to set a string equal to "Table" & Sheetname?

Dim oNewRow As ListRow, rng As Range, tbl As String

Set tbl = "Table" & sht

Set rng = Worksheets(sht).Range(tbl)
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
With ws
        oNewRow.Range.Cells(1, 1).Value = Me.box1.Value
        oNewRow.Range.Cells(1, 2).Value = Me.box2.Value
        oNewRow.Range.Cells(1, 3).Value = Me.box3.Value
        oNewRow.Range.Cells(1, 4).Value = Me.box4.Value
        oNewRow.Range.Cells(1, 5).Value = Me.box5.Value
        oNewRow.Range.Cells(1, 6).Value = Me.box6.Value
End With

if the combobox selection is the same as the sheetname then just get the selected value and combine with Table & — Sorceri, Oct 22 '19 at 22:18

score 0 · Answer 1 · answered Oct 22 '19 at 22:19

0

This will select 2 tables - table1 and table2, the one after the other:

Sub TableSelector()

    Dim tableName As String
    Dim i As Long

    For i = 1 To 2
        Worksheets(1).Range("table" & i).Select
        Stop
    Next i        

End Sub

Just make sure to pass the i from a UserForm:

answered Oct 22 '19 at 22:19

Vityata

42,633
8
55
100

Tried this, but am getting a selection error ```For i = 1 To 1500 If i = sht Then Worksheets(sht).Range("Table" & i).Select End If Next i``` With sht being the sheet name (which is a number matching the building address) – Liworker Oct 22 '19 at 22:56
@Liworker - write `Worksheets(i).Select` on the line above the previous select. Thus, the worksheet would be selected and then the table. – Vityata Oct 22 '19 at 23:23
Got it working just by removing the ```Worksheets(sht).Range("Table" & i).Select``` line. Thanks for your help – Liworker Oct 23 '19 at 15:40
@Liworker - good that you managed yourself. In general, just keep in mind that `Select` and `Activate` are considered a bit bad practice in VBA - [here is how to avoid them](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Vityata Oct 23 '19 at 18:00

Sorceri · Answer 2 · 2019-10-22T22:56:48.233

0

Maybe I am misreading this but it sounds like you want to get the sheet name from the combobox to select the right table.

'updated as it looks like your logic is in the actual form

'Lets say your combobox name is ComboBox1 (since oyu did not supply that)

Dim oNewRow As ListRow, rng As Range, tbl As String
Dim sht As String
sht = Me.ComboBox1.Text
tbl = "Table" & sht

Set rng = Worksheets(sht).Range(tbl)
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
With ws
    oNewRow.Range.Cells(1, 1).Value = Me.box1.Value
    oNewRow.Range.Cells(1, 2).Value = Me.box2.Value
    oNewRow.Range.Cells(1, 3).Value = Me.box3.Value
    oNewRow.Range.Cells(1, 4).Value = Me.box4.Value
    oNewRow.Range.Cells(1, 5).Value = Me.box5.Value
    oNewRow.Range.Cells(1, 6).Value = Me.box6.Value

End With

edited Oct 22 '19 at 22:56

answered Oct 22 '19 at 22:32

Sorceri

7,870
1
29
38

Yeah, sorry I have a lot of code and forgot a few things when I cut it down to post. Essentially, The user selects the region, location, then building address. The numerical part of the address is what is tied to the sheets (sht). My hope is to have it open the sheet for that building, then enter the data into the table on that sheet. Currently the ```tbl = "Table" & sht``` gives an error saying an object is needed. I changed it a little bit based on the answer above to include a for loop that when i = sht then it selects it (See my response to above answer), but that also gives an error – Liworker Oct 22 '19 at 23:12
@Liworker You may want to post a sample set of data so we can see how the address is and the sheet is named. This would help in providing a more robust answer. – Sorceri Oct 23 '19 at 14:37
Ended up having to change it around a little bit due to a change in how they wanted it to work (not all buildings are referenced by the numerical part of the address) but got it working by removing the line to select the table. Thanks for your help – Liworker Oct 23 '19 at 15:39

Is there a way to select a table based on a userform input?

2 Answers2