Excel VBA ComboBox error

Question

I am trying to fill a comboxbox located in sheet "AAA" with data in Sheet "BBB"

x = Application.WorksheetFunction.Max(Sheets("BBB").Range("m2:m200")) + 1
Sheets("AAA").Shapes("Drop_Leg").Select
With Selection
    .ListFillRange = "='BBB'!n2:n" & x
End With

I am getting the error

"Requested Shapes are locked for selection".

I have tried different approaches, but cannot get it to work. This used to be a dropbox, but I was asked to change it to a combo.

Thanks in advance

Did you try without using .select? Like Sheets("AAA").Shapes("Drop_Leg").ListFillRange = "='BBB'!n2:n" & x. This can occur when the shape is not visible or something, then it obviously cannot be selected. — Pᴇʜ
– Pᴇʜ, Commented May 24, 2017 at 8:14
Yeap, I have tried. I get the error: Object doesn't support this property or method... — Paulo Alves
– Paulo Alves, Commented May 24, 2017 at 8:29
is it a Drop-Down (combo-box type user-form) ? or Active-X combo-box ? — Shai Rado
– Shai Rado, Commented May 24, 2017 at 8:31
Out of curiousity, what is the maximum value in M2:M200? (It seems strange that you are using the maximum in that range to determine how many cells to use in the next column. E.g. if the maximum in M2:M200 is 497, are you really wanting to use N2:N498 to populate the combobox?) — YowE3K
– YowE3K, Commented May 24, 2017 at 8:32
This should work without selecting if it is of type user-form and no ActiveX: Sheets("AAA").Shapes("Drop_Leg").ControlFormat.ListFillRange = "='BBB'!n2:n" & x — Pᴇʜ
– Pᴇʜ, Commented May 24, 2017 at 8:37

Shai Rado · Accepted Answer · 2017-05-24 08:48:41Z

2

Try the code below to populate your Active-X Combo-Box.

There's no need to use Select to populate the Combo-Box, it only slows down your code's run-time.

Code

Option Explicit

Sub FillCombo()

Dim x           As Long
Dim ComboRng    As Range

x = Application.WorksheetFunction.Max(Sheets("BBB").Range("M2:M200")) + 1
Set ComboRng = Sheets("BBB").Range("N2:N" & x) '<-- set the Range

With Sheets("AAA").OLEObjects("Drop_Leg").Object
    .Clear ' clear before adding new values
    .List = ComboRng.Value ' populate the list with values from Column N
End With

End Sub

answered May 24, 2017 at 8:48

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

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

6 Comments

Paulo Alves Over a year ago

Thanks. for the help, but still not there.Get the error: "Unable to gget the OLEObjects property of the worksheet class" The reason why I used the active X was because the form comboBox for some reason doesn't let me type.

Shai Rado Over a year ago

@PauloAlves you copied my entire code and which line did you get your error ?

Paulo Alves Over a year ago

With Sheets("AAA").OLEObjects("Drop_Leg").Object

Pᴇʜ Over a year ago

@PauloAlves That means that there is no ActiveX combobox called Drop_Leg in sheet AAA. Check your sheet name and combobox name again.

Paulo Alves Over a year ago

Solved. restart excel and voilá! your code is working perfectly!! Thanks

|

Collectives™ on Stack Overflow

Excel VBA ComboBox error

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related