VBA problem

  Les 12:15 29 Apr 07
Locked

I have up to 600 items which I wish to put in a Listbox (forms) so that there are 3 columns of 200 items each.

Establishing the three columns was easy but I haven't a clue of how to put the data into them

Help appreciated.

  VoG II 13:40 30 Apr 07

Something like:

UserForm1.ListBox1.RowSource = "'Sheet1'!A1:A12"

  Les 21:26 30 Apr 07

Thanks for that piece of code - something else I've learned. I'll play with that - I've have used a for next loop to read the data into the listboxes. I suspect that your method would be quicker.

  VoG II 21:35 30 Apr 07

There are other ways of doing this. My example assumed that you wanted to do this using code each time you ran the program - if not I think it can be built in to the Userform.

As to what is faster - there is something that you can buy for timing Excel formula code - I didn't bother but if you are interested I can have a ferret. But here is a completely unrelated example of how to time your code:

Sub HyperlinkConverter()
Dim lastrow As Long, i As Long, web As String, t As Date
t = Now()
Application.ScreenUpdating = False
Application.Calculation = xlManual
lastrow = Cells(Rows.Count, 3).End(xlUp).Row
For i = 4 To lastrow
web = Cells(i, 3).Value
ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 3), Address:=web, TextToDisplay:=web
Next i
Application.ScreenUpdating = True
Application.Calculation = xlAutomatic
MsgBox "Hyperlink Converting Run Time: " & Format(Now() - t, "hh:mm:ss")
End Sub

  Les 08:26 01 May 07

The method I used just after posting the original request was to use 4 listboxes to contain the all the data and so use the full width of the screen - giving the maximum of visible data without scrolling. Selection of an item in 1 listbox then involved the disabling of the other 3 so that only one item of data could be selected. To get the data into the listboxes I used a For/Next loop which switched data to the various listboxes as the value of the loop increased. It works Ok and doesn't appear to be slow at all.

The reference to speed was about something I seem to remember about the difference in speed between various examples of code used to move data around.

I learned VB back in the early days of it's being released - hence my simplistic way of doing things - old dogs and new tricks is a saying that covers me I think 8-))

I am about to try out your snippet of code I appreciate your help, as indeed many who use this forum have reason to so do - many thanks.

  Les 08:37 01 May 07

OK. I have tried your code and it works fine - no noticable change in the speed of loading - both appear to be the same - fast!. I suppose that my reference to difference in speed is less noticeable in these days of fast processors. Again, thanks.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands-on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Prehistoric Britain is laid out in these Royal Mail stamp illustrations

Best running headphones | Best sport & fitness headphones: 4 brilliant pairs of wireless…