Solved: Populating a userform....

[Papadopoulos]

This should be relatively straightforward but I can't seem to get it to behave.
The code below is part of the initialization that populates a listbox on a userform.


With ListBox_Items
For Each cell In Range(Cells(2, 1), Cells(ActiveSheet.UsedRange.Rows.Count, 1))

I want to reference a Worksheet by name but my attempt... (Worksheets("VersHist").UsedRange.Rows.Count, 1) fails.
What would be a good way of going about this?

Thanks a bunch,
David

[lucas]

Welcome to the board.

can you show the initialize code so we can reproduce your userform.

You can format your code for the forum by selecting it when posting and hitting the vba button.

It would be nice if you could upload the workbook with the userform.

hit go advanced and scroll down till you see the button that says manage attachments.

[Papadopoulos]

Welcome to the board.

can you show the initialize code so we can reproduce your userform.

You can format your code for the forum by selecting it when posting and hitting the vba button.

It would be nice if you could upload the workbook with the userform.

hit go advanced and scroll down till you see the button that says manage attachments.
It's not actually my code. I am using the code from the List Box Reverse Order (Article 1078) example on your site. I just want to reference a sheet that I am not viewing a (without switching to that sheet)
I didn't send my actual code because it's part of a 3mb file that is full of embarrassing code (Currently attempting to make the whole project much more flexible and streamlined)
If this doesn't do it I will pull out the offending pieces and send them.

Thanks!

[lucas]

Ok David, can you give me a few minutes to try to look up the article and we will see what we can do.

In the meantime, and it doesn't matter who's code it is, if you could just post the initialize routine from the code that would save me some time.

[Papadopoulos]

I attached the Excel file that demonstrates the issue.

Thanks

[Papadopoulos]

Sorry, I shouldn't have asked you to do that.

[GTO]

Maybe:

For Each cell In Range(ThisWorkbook.Worksheets("VersHist").Cells(2, 1), _
ThisWorkbook.Worksheets("VersHist").Cells(Rows.Count, 1).End(xlUp))
Hope that helps,

Mark

[lucas]

Hi Mark.

Attached is the kb workbook with your sheet name and Mark's code added.

ps. When you get through with your question on this thread David, you can mark your thread solved using the thread tools at the top of the page.

That keeps folks like Mark from reading the entire thread just to find that it's already been solved.

[GTO]

:hi: Back atchya Steve. Been meaning to send a Howdy your way. Have to logout for a while (leaving work late on my Friday, I must have brain damage...), but will see if you're about later :-)

Mark

[lucas]

David, here's your file back, fixed and working. Attached.

[Papadopoulos]

Thanks you all!
You guys rock.

[mdmackillop]

Rather than looping to fill a Combobox or List, use the List function


Private Sub UserForm_Initialize()
Dim Rng As Range

With ThisWorkbook.Worksheets("VersHist")
Set Rng = Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Resize(, 3)
End With

ListBox_Items.List() = Rng.Value

End Sub