replacing repetitive code with more efficient code

[MeisterConrad]

In redesigning a Workbook, I have a UserForm with a ListBox that lists various Tables. I want to be able to click on any of the Table names in the ListBox as a means of selecting that Table for view/editing.
I'm trying to clean up the following code (old code for an older version of the Workbook) and make it more efficient:

Private Sub ListBox1_Click()

If ListBox1.Selected(0) = True Then

Range("Table1").Select

Range("D" & Rows.Count).End(xlUp).Select

End If

If ListBox1.Selected(1) = True Then

Range("Table2").Select

Range("R" & Rows.Count).End(xlUp).Select

End If

If ListBox1.Selected(2) = True Then

Range("Table3").Select

Range("AF" & Rows.Count).End(xlUp).Select

End If

'... and on and on through all the items in the ListBox


End Sub

I'm replacing it with:

Private Sub ListBox1_Click()

For i = 1 To 35

If ListBox1.Selected(i) = True Then

Range("LedgerTable" & i).Select

Range("E" & Rows.Count).End(xlUp).Select

End If

Exit For

Next i

End Sub

...But it isn't working. What am I doing wrong?

Thanks for the help.

 

[Jeffrey Mahoney]

Try this. I know it's simple. This assumes you want to select the last item in the first column of each table. IF you want the 3rd column or something like this then try something like this:
Set Cel = Range(TableName).Resize(1, 1).offset(0,1)

Private Sub ListBox1_Click()
  Dim Sel As Long
  Dim TableName As String
  Dim Cel As Range
  
  Sel = ListBox1.Selected(1)
  TableName = "Table" & Sel + 1
  Set Cel = Range(TableName).Resize(1, 1)
  Cells(Cells.Rows.Count, Cel.Column).End(xlUp).Select
  
End Sub

 

[MeisterConrad]

Maybe I am misunderstood. The ListBox is essentially a menu of Tables. The ListBox is automatically populated with Table names the user has defined elsewhere. I want to be able to click on one of the names, resulting in the chosen table to be displayed with the first cell in the last row selected. Subsequent clicking on another name in the ListBox should result in the display of that Table, and so forth.
Your sample code restricts Sel to only the second item in the ListBox; it doesn't take into account the other items in the ListBox.
I think my problem is somewhere in the combination of If/EndIf and For/Next.
I've tried altering the code below (making If = False rather than True), but I still keep getting an error of "Next without For".
I used to use multiple If/Then statements; one for each item in the ListBox. Now I'm trying to use fewer lines of code to accomplish the same thing.
I used to have all the associated Tables on one Worksheet. Now, they're all each on a separate Worksheet -in the same range on each Worksheet.
So the line Range("E" & Rows.Count).End(xlUp).Select should work no matter which ListBox item is clicked.
- a thought: Do I need to include the name of the Worksheet in addition to the name of the Table?

Private Sub ListBox1_Click()
Dim AcNum As Integer
For i = 1 To 35
    If ListBox1.Selected(i) = False Then Next i
    Else
    Range("LedgerTable" & i).Select
    Range("E" & Rows.Count).End(xlUp).Select
    Exit For
End If

End Sub

[/CODE]

 

[Jeffrey Mahoney]

The code I provided assumed that all your table names were Table1, table2, table3 and so on. Each time the user selects a different item in the listbox it was meant to select a table based on the item number. If you have the Table Names in the listbox then we should pull that name from the listbox item

The example below uses the table name in the list: TableName = ListBox1.List(X, 0). If the table name column is different then change the Zero to something greater.

Private Sub UserForm_Initialize()
  Dim X As Long
  
  For X = 0 To 10
    ListBox1.AddItem CStr("Table" & X + 1)
  Next X

End Sub



Private Sub ListBox1_Click()
  Dim Sel As Long
  Dim TableName As String
  Dim Cel As Range
  Dim X As Long
  
  For X = 0 To ListBox1.ListCount
    If ListBox1.Selected(X) = True Then
      Sel = X
      TableName = ListBox1.List(X, 0)
      Exit For
    End If
  Next X
  
  Set Cel = Range(TableName).Resize(1, 1)
  Cells(Cells.Rows.Count, Cel.Column).End(xlUp).Select
  
End Sub

 

[Akuini]

resulting in the chosen table to be displayed with the first cell in the last row selected.

Try this:

Private Sub ListBox1_Click()
Dim tx As String
    With ListBox1
        If .ListIndex > -1 Then tx = .List(.ListIndex, 0)
    End With
    
    If tx <> "" Then
        Range(tx).Parent.Activate  'activate the sheet where the table is located
        With ActiveSheet.ListObjects(tx).DataBodyRange
             .Cells(.Rows.Count, 1).Select 'select first cell, last row
        End With
    End If

End Sub

Are you using a multi-select listbox? it should be a single-select listbox.

 

[MeisterConrad]

The code I provided assumed that all your table names were Table1, table2, table3 and so on. Each time the user selects a different item in the listbox it was meant to select a table based on the item number. If you have the Table Names in the listbox then we should pull that name from the listbox item

The example below uses the table name in the list: TableName = ListBox1.List(X, 0). If the table name column is different then change the Zero to something greater.

Private Sub UserForm_Initialize()
  Dim X As Long
 
  For X = 0 To 10
    ListBox1.AddItem CStr("Table" & X + 1)
  Next X

End Sub



Private Sub ListBox1_Click()
  Dim Sel As Long
  Dim TableName As String
  Dim Cel As Range
  Dim X As Long
 
  For X = 0 To ListBox1.ListCount
    If ListBox1.Selected(X) = True Then
      Sel = X
      TableName = ListBox1.List(X, 0)
      Exit For
    End If
  Next X
 
  Set Cel = Range(TableName).Resize(1, 1)
  Cells(Cells.Rows.Count, Cel.Column).End(xlUp).Select
 
End Sub

So, after taking some time to try out your samples and understand them, I tried the following modified version:

Private Sub ListBox1_Click()
Dim Sel As Long
Dim TableName As String
Dim Cel As Range

For X = 0 To 34
If ListBox1.Selected(X) = True Then
Sel = ListBox1.Selected(X)
TableName = "LedgerTable" & (Sel + 1)
Set Cel = Range(TableName).Resize(1, 1)
Cells(Cells.Rows.Count, Cel.Column).End(xlUp).Select
Exit For
End If
Next X

End Sub

In trying this a few times, I got two different debugger/error messages:

I can understand how this is an error message because it says “failed” right there in it. I got this error multiple times.
Range(TableName).Resize(1, 1) = <Method ‘Range of object’_Global’ failed>

And

This seems closer to correct, though I could be wrong, because the range that should get selected is called LedgerTable, but I don’t understand the (). I got this error multiple times as well.

TableName = “LedgerTable()”

The problem with this is that it reads like “LedgerTable” is a list with Items in it “()”. It’s not that there’s supposed to be a number in the “()”; there is supposed to be a number after “LedgerTable”, but there shouldn’t be ANY “()”.



I even tried swapping out the last 2 lines with [Range(TableName).Select], and I still get debugger/error messages.

I understand the DIMing of the different variables as different things, because the LONG needs to be identified in order to correctly create the STRING, and the STRING needs to be assembled in order for the RANGE to be properly defined. It seems like excel is not understanding that we're assembling the name of the desired Table.

But I confess I don’t quite understand those last 2 lines about setting Cel and selecting the final cell.

But I also wonder, is it a problem that each of those LedgerTables is on a different Worksheet? Is that something that the code needs to address before it can select the correct cell?

 

[Fuji]

UserForm1 with 1 ListBox(ListBox1) & 1 CommandButton(CommandButton1)
To UserForm1 code module.

Private Sub UserForm_Initialize()
    Dim tbl As ListObject, ws As Worksheet
    With Me.ListBox1
        For Each ws In Worksheets
            For Each tbl In ws.ListObjects
                .AddItem ""
                .List(.ListCount - 1, 0) = tbl.Name
                .List(.ListCount - 1, 1) = ws.Name
            Next
        Next
        .ColumnCount = 1
    End With
End Sub

Private Sub CommandButton1_Click()
    Dim wsName As String, tblName As String
    With Me.ListBox1
        If .ListIndex = -1 Then Exit Sub
        With Sheets(.List(.ListIndex, 1)).ListObjects(.List(.ListIndex, 0))
            Application.Goto .ListRows(.ListRows.Count).Range(1)
        End With
    End With
    Unload Me
End Sub