Rowsource reference for Combobox

[bmacd615]

This code is in my Private Sub UserForm_Initialize().
I need to specify the range AND SHEET in the following code. The range portion of this code works. This routine will get the correct RANGE, but it does not select the correct sheet. It uses the range of whichever sheet is active when I run the form. How can I make the code get the right SHEET.

For i = 1 To 8
Me.Controls("Combobox" & i).RowSource = Worksheets("GAB").Range("a1:a5").Address
Next i

 

[bmacd615]

I guess I found a simple solution. I used Worksheets("GAB").Activate to activate the sheet before the For Next routine, and then activated the "home page" work sheet after the For Next routine.

Application.ScreenUpdating = False
Worksheets("GAB").Activate
For i = 1 To 8
Me.Controls("Combobox" & i).RowSource = Worksheets("GAB").Range("a1:a5").Address
Next i
Worksheets("Input&Output").Activate
Application.ScreenUpdating = True

This works. Is there a better way?

 

[Norie]

Why not just include the field in the RowSource?

Private Sub UserForm_Initialize()
Dim I As Long
    
    Application.ScreenUpdating = False
    For I = 1 To 8
        Me.Controls("Combobox" & I).RowSource = "GAB!A1:A5"
    Next I
    Application.ScreenUpdating = True
    
End Sub

 

[jim may]

Is there an issue by stating 1 to 8
and "GAB!A1:A5" (5) ?

 

[Norie]

ADVERTISEMENT

Jim

What would you be using 1-8 for?

 

[bmacd615]

I am using the For next loop because I have 8 boxes that I want the user to fill with eight items from a list that occupies the a1:a5 range (when completed there will be about 30 items A1:a30 to select from and fill the boxes).

 

[ZVI]

ADVERTISEMENT

Comboboxes also could be populated without RowSource property:

<font face=Courier New>
Private Sub UserForm_Initialize()
  Dim i As Long, arr()
  arr = Worksheets("GAB").Range("A1:A5").Value
  For i = 1 To 8
    Me.Controls("Combobox" & i).List = arr
  Next i
End Sub</FONT>

 

[mikerickson]

Row Source isn't supported on my machine, but you might try

Application.ScreenUpdating = False
'Worksheets("GAB").Activate
For i = 1 To 8
    Me.Controls("Combobox" & i).RowSource = Worksheets("GAB").Range("a1:a5").Address(, , , True)
Next i
Worksheets("Input&Output").Activate
Application.ScreenUpdating = True

 

[bmacd615]

All of the replies work.
Noire and Jim May's suggestions of

Me.Controls("Combobox" & I).RowSource = "GAB!A1:A5"

works fine now, but that was a variation I tried earlier and would give an error. But it works now and is simple!

ZVP use of an array variable works simply also.

Mikerickson's works fine but I need to understand a little more about the address parameters.

Thanks everyone. This forum is excellent!