Fill Combobox list from a Row

[g-man97]

I have a table with the values for the combobox in row 2, columns 11-76. I picked up the following code to fill the combobox. Of course it fills based on the data being all in a column.


Private Sub UserForm_Initialize()
'define variable types
Dim rng As Range
Dim I As Long
Application.ScreenUpdating = False
'Active sheet where office list is
Sheets("OfficeLocations").Activate
'enter loop
For I = 2 To 67
'data is in column "B"
Set rng = ActiveSheet.Range("B" & I)
InputForm.ComboBox1.AddItem rng.Value
Next I
Sheets("MainMenu").Select
Range("G11").Select
End Sub

I'd really appreciate a suggestion / example / nudge on how to get the data for combobox from a row.

As always, thanks in advance for the suggestions.


G-Man

 

[vconfused]

Change the For loop to go from 11 to 67 (rather than 2 to 67)
Then rather than using the Range(), use Cells(). Using Cells, you can call the columns more easily by numbers (i.e. 11-67).
As long as you have the sheets and the userform named InputForm, the below works.

Private Sub UserForm_Initialize()
'define variable types
Dim rng As Range
Dim I As Long
Application.ScreenUpdating = False
'Active sheet where office list is
Sheets("OfficeLocations").Activate
'enter loop
For I = 11 To 67
'data is in column "B"
Set rng = ActiveSheet.Cells(2, I)
InputForm.ComboBox1.AddItem rng.Value
Next I
Sheets("MainMenu").Select
Range("G11").Select
End Sub

 

[g-man97]

Vconfused, thanks a million, it worked like a charm!! I'll definitely store that one away for future reference! I'm sure it'll come in handy!

Thanks again.


G-Man