VBA ListFillRange populate using dynamic range from different sheet

Question

I am trying to populate a combobox drop down list when a different combobox "combobox2" is clicked. I am receiving the error Runt time error 438 Object doesnt support this property or method. I am also sources the listfillrange values from a seperate worksheet. I have included my code below.

Private Sub ComboBox2_Click()

     Dim N As Long

     N = Worksheets("Regions-Offices").Cells(1, 8).End(xlDown).Row

     ActiveSheet.Cells(1, 2).Value = N

     Worksheets("Global").OLEObjects("ComboBox3").Object.ListFillRange = Worksheets("Regions-Offices").Range("H1:" & N).Address

End Sub

Also, you can use offset from H1 instead of using N or offset N — Nathan_Sav
– Nathan_Sav, Commented Sep 24, 2019 at 21:00
@Nathan_Sav I changed the range to be Range("H1:" & "H" & N) however now i am getting error 438 Object doesnt support this property or method — user9730643
– user9730643, Commented Sep 24, 2019 at 21:05
range("h1:h" & n) I would have tried. Which line is the error on, just to be sure. There are some arguments to .address too — Nathan_Sav
– Nathan_Sav, Commented Sep 24, 2019 at 21:06
@Nathan_Sav the error is on the following line "Worksheets("Global").OLEObjects("ComboBox3").Object.ListFillRange = Worksheets("Regions-Offices").Range("H1:H" & N).Address". Also evenb with Address removed error 438 is still there — user9730643
– user9730643, Commented Sep 24, 2019 at 21:08

DisplayName · Accepted Answer · 2019-09-25 06:57:11Z

1

use External parameter of Address property:

Worksheets("Global").OLEObjects("ComboBox3").ListFillRange = Worksheets("Regions-Offices").Range("H1:H" & N).Address(, , , True)

answered Sep 25, 2019 at 6:57

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nathan_Sav · Accepted Answer · 2019-09-24 21:22:14Z

0

The worksheet name isn't evaluated in the return of address so

"'Regions-Offices'!H1:H" & N

edited Sep 24, 2019 at 21:22

answered Sep 24, 2019 at 21:14

Nathan_Sav

8,5762 gold badges18 silver badges22 bronze badges

Collectives™ on Stack Overflow

VBA ListFillRange populate using dynamic range from different sheet

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest