How to reference a specific sheet vs using active sheet?

Question

I am trying list records from another sheet instead of ActiveSheet. Is there a way to achieve this? Thanks.

Public Sub CommandButton4_Click()
    ListBox1.ColumnHeads = True
    ListBox1.ColumnCount = 7
    ListBox1.RowSource = "A2:G1000"

    'Count the listbox rows when populated

    With ActiveSheet             
        lastrow = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Rows.Count
        TextBox3 = lastrow
    End With
End Sub

Cf. [How to avoid using `Select` in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba/10717999#10717999) and try to fully qualify your range references :-) — T.M., Oct 28 '18 at 09:06

score 1 · Accepted Answer · edited Oct 28 '18 at 11:23

1

Like this:

Public Sub CommandButton4_Click()
ListBox1.ColumnHeads = True
ListBox1.ColumnCount = 7
ListBox1.RowSource = "A2:G1000"

Dim ws As Worksheet
Set ws = Sheets("Sheet1")

'Count the listbox rows when populated

With ws             
    lastrow = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Rows.Count
    TextBox3 = lastrow
End With
End Sub

edited Oct 28 '18 at 11:23

Alex P

12,249
5
51
70

answered Oct 28 '18 at 09:40

Lambik

520
1
6
13

How to reference a specific sheet vs using active sheet?

1 Answers1