VBA iactivate worksheet userform

Question

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!

Dmitry Pavliv · Answer 1 · 2014-04-01T22:15:59.333

2

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.

edited Apr 01 '14 at 22:15

answered Apr 01 '14 at 21:48

Dmitry Pavliv

35,333
13
79
80

The above lines after `UserForm1.Show` will not run till the time the userform is unloaded as the userform will be opened in modal mode ;) And hence it is good to use the `UserForm_Initialize()` method as I mentioned below – Siddharth Rout Apr 01 '14 at 21:53
I know it, I don't mentioned it in my answer, since you already have this information in your answer. I concentrated on `RowSource` property:) – Dmitry Pavliv Apr 01 '14 at 21:54
I posted 11 seconds after you ;) – Siddharth Rout Apr 01 '14 at 21:55
yes, but I concentrated on another part of answer, and after seeing that you already has this point in your answer, I did not want to copy your answer – Dmitry Pavliv Apr 01 '14 at 21:57
That's ok ;) You can mention that in your answer so that it is complete :) – Siddharth Rout Apr 01 '14 at 21:58
1

+ 1 Makes complete sense now :) – Siddharth Rout Apr 01 '14 at 21:59

score 1 · Answer 2 · answered Apr 01 '14 at 21:48

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

Roger Barreto · Accepted Answer · 2014-04-01T22:30:14.030

1

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

edited Apr 01 '14 at 22:30

answered Apr 01 '14 at 21:50

Roger Barreto

2,004
1
17
21

There is a problem with this method. The three lines after `UserForm1.Show` will not run till the time the userform is unloaded as the userform will be opened in modal mode ;) And hence it is good to use the `UserForm_Initialize()` method as I mentioned below – Siddharth Rout Apr 01 '14 at 21:52
Indeed, will change the order, I would suggest to use the initiallze also but depending of the circusntances this is acceptable solution. :) – Roger Barreto Apr 01 '14 at 21:55
+ 1 Indeed now that you have moved `UserForm1.Show` to the end :) – Siddharth Rout Apr 01 '14 at 21:57
Er, sorry to be a pain but with you need to move `.Activate` before `UserForm1.ListBox1.RowSource` else you will get unexpected results if some other sheet is activated :) or use `.Name` as I did – Siddharth Rout Apr 01 '14 at 22:01
Actually it will not be a problem once Im inside a With Block referencing the Sheet2. – Roger Barreto Apr 01 '14 at 22:04
There is no absolute address for such situation ? Guessed that address from a sheet different from activated would be absolute – Roger Barreto Apr 01 '14 at 22:05
`.Range(TagString).Address` will not give you something like "'Sheet2'!$A$1:$A$10". it will give you only "$A$1:$A$10" – Siddharth Rout Apr 01 '14 at 22:06
`UserForm1.ListBox1.RowSource = .Name & "!" & .Range(TagString).Address` would give you the correct result :) Or if you do not want to use `.Name` then move the `.Activate` before `UserForm1.ListBox1.RowSource` line – Siddharth Rout Apr 01 '14 at 22:09
Ouch! No... Not `xlDown` You had a perfect answer. I would recommend rolling back to your previous version... – Siddharth Rout Apr 01 '14 at 22:28
Double OUCH `.UsedRange`? http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba – Siddharth Rout Apr 01 '14 at 22:31
Anyways. This is my last comment else I will keep on commenting LOL. I still feel that you had a perfect answer earlier which needed no change :) Off to bed now... already 4 AM in this part of the globe... – Siddharth Rout Apr 01 '14 at 22:34
I know usedrange has its weirds but its better than lastrow, right ? Have to dig around for knowing how the VBA Excel engine could be best used for traversing – Roger Barreto Apr 01 '14 at 22:36
IMHO, the term `UsedRange` should be abolished :D BTW `Dim NumTags As Integer` should be `Dim NumTags As Long` as it may give you errors in xl2007+ ;) ok.. seriously this was my last comment!!! :D Gnite – Siddharth Rout Apr 01 '14 at 22:38

VBA iactivate worksheet userform

3 Answers3