Solved: Workbook_Open loop??

[gringo287]

Hi,

I received some awesome help yesterday from CodeNinja and not sure if i would be breaking the forum rules if i were to re open a solved thread, so i thought id better start a new one. Pretty sure im missing something simple.

I have Four sheets that visually will look idnetical but will all have different options within a combobox. Ive essentally duplicated CodeNinjas code and made my best attempt at looping (without any idea if its even possible in workbook_open) it to produce the desired lists on the individual sheets. im not getting any errors, but only Combobox1 is working?



[VBA]Private Sub Workbook_Open()
Dim l As Long
Dim s As String

For l = 40 To Sheet1.Range("B65536").End(xlUp).Row
s = Sheet1.Cells(l, 1)
Sheet1.ComboBox1.AddItem (s)

Next


For l = 40 To Sheet5.Range("B65536").End(xlUp).Row
s = Sheet5.Cells(l, 1)
Sheet5.ComboBox2.AddItem (s)

Exit For

Next

For l = 40 To Sheet6.Range("B65536").End(xlUp).Row
s = Sheet6.Cells(l, 1)
Sheet6.ComboBox3.AddItem (s)

Exit For

Next

For l = 40 To Sheet7.Range("B65536").End(xlUp).Row
s = Sheet7.Cells(l, 1)
Sheet7.ComboBox4.AddItem (s)

Exit For

Next

End Sub[/VBA]

[patel]

please remove all exit for

[Bob Phillips]

[VBA]Private Sub Workbook_Open()

With Sheet1
.ComboBox1.List = Application.Transpose(.Range(.Range("B40"), .Range("B40").End(xlDown)))
End With

With Sheet5
.ComboBox2.List = Application.Transpose(.Range(.Range("B40"), .Range("B40").End(xlDown)))
End With

With Sheet6
.ComboBox3.List = Application.Transpose(.Range(.Range("B40"), .Range("B40").End(xlDown)))
End With

With Sheet7
.ComboBox4.List = Application.Transpose(.Range(.Range("B40"), .Range("B40").End(xlDown)))
End With
End Sub[/VBA]

[patel]

So it's better

[gringo287]

Thanks guys. I used patel's suggestion. your suggestion worked xld, but I needed to keep the offset.

[Bob Phillips]

Keep what offset? they do the same things, one with an inefficient loop, one without.

[gringo287]

webBrowersorry, xld, I didn't give a full list explanation of my initial query that codeninja helped me with. I'm basically creating a categorised YouTube tutorial viewer for my work place. In order to eliminate any unnecessary searching, I have a categorised list of all the links. The user will chose the desired sheet tab and then choose from a combobox selection to view the tutorial from a Web browser in excel. The offset is just so the title of the tutorials shows instead of the URL.

[gringo287]

sorry wrote this on my phone, got a bit jumbled