PDA

View Full Version : Solved: Workbook_Open loop??



gringo287
07-14-2012, 12:12 PM
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?



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

patel
07-15-2012, 01:42 AM
please remove all exit for

Bob Phillips
07-15-2012, 02:18 AM
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

patel
07-15-2012, 05:09 AM
So it's better

gringo287
07-17-2012, 02:29 AM
Thanks guys. I used patel's suggestion. your suggestion worked xld, but I needed to keep the offset.

Bob Phillips
07-18-2012, 02:01 AM
Keep what offset? they do the same things, one with an inefficient loop, one without.

gringo287
07-18-2012, 02:27 AM
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
07-18-2012, 02:29 AM
sorry wrote this on my phone, got a bit jumbled