CODEWITHSUNDEEP
excelvba
user3486991
user3486991

Reputation: 463

VBA iactivate worksheet userform

Trying to learn VBA in Excel context. I'd like to populate a listbox from a range in column A, on Sheet2 (some text strings in the column) as in the following code

Public Sub Test()
Dim NumTags As Integer
Dim TagString As String

NumTags = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
UserForm1.Show
TagString = "A1:A" & NumTags
'Worksheets("Sheet2").Activate
UserForm1.ListBox1.RowSource = TagString
End Sub

If I call this sub while Sheet1 is activated, it will not populate the listbox properly, or at least not every time. If I uncomment that Worksheets("Sheet2").Activate line, everything works properly, but of course it switches activation to Sheet2, which I don't want.

Question: Why is that? Am I populating the ListBox in some incorrect/shabby way?

Thanks for your help!

Upvotes: 0

Views: 121

Answers (3)

Roger Barreto
Roger Barreto

Reputation: 2284

Try to reference your sheet in an object, try to search from the lowest used range for optimization.

Public Sub Test()
    Dim NumTags As Integer

    With Worksheets("Sheet2")
        NumTags = .Cells(.Usedrange.Rows.Count, 1).End(xlUp).Row
        UserForm1.ListBox1.RowSource = .Name & "!A1:A" & NumTags
        .Activate
    End With
    UserForm1.Show
End Sub

Upvotes: 1

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35853

Use either:

TagString = "'Sheet2'!A1:A" & NumTags
UserForm1.ListBox1.RowSource = TagString

or

UserForm1.ListBox1.List = Worksheets("Sheet2").Range("A1:A" & NumTags).Value

otherwise in your code you always referce to range "A1:A" & NumTags from active sheet

Note, that line UserForm1.Show should be after UserForm1.ListBox1.RowSource = TagString (or UserForm1.ListBox1.List = ...), otherwise untill you run code again, your ListBox1 would be empty (or contains previous values).

Also I suggets you to take a closer look at @SiddharthRout's answer - he has a very good point about refactoring your code.

Upvotes: 2

Siddharth Rout
Siddharth Rout

Reputation: 149297

Am I populating the ListBox in some incorrect/shabby way?

Yeah sort of. You can put the entire code in the UserForm_Initialize event.

So your above code looks like this

Public Sub Test()
    UserForm1.Show
End Sub

And then in the Userform Code Area

Private Sub UserForm_Initialize()
    Dim NumTags As Long
    Dim TagString As String

    With ThisWorkbook.Sheets("Sheet2")
        NumTags = .Range("A" & .Rows.Count).End(xlUp).Row
        TagString = "A1:A" & NumTags
        ListBox1.RowSource = .Name & "!" & TagString
    End With
End Sub

Upvotes: 1

Related Questions