VBA Excel cannot use Controls library

Question

I'm trying to write a code like this below. but as per the print, .Controls option is no showing and i'm receiving Compile error: Method or data member not found

any idea on how this is happening and how to fix it ? many hours looking for a solution without success. thanks

Private Sub test()
Dim i As Integer
i = 1
PLN_ESTOQUE.Controls("combobox" + i).Value = "test"
End Sub

What kind of object is PLN_ESTOQUE? Is it a User Form control? — FaneDuru
– FaneDuru, Commented Nov 21, 2020 at 20:43
Looking to what Intellisense shows, it looks to be a Worksheet object. If so, this object does not have any controls property... If I am right, what kind of control is the ComboBox in discussion? A sheet Form type or an Activex ComboBox? — FaneDuru
– FaneDuru, Commented Nov 21, 2020 at 20:50
Hi PLN_ESTOQUE is a worksheet and Combobox is and ActiveX object — Lucas
– Lucas, Commented Nov 21, 2020 at 21:30

FaneDuru · Accepted Answer · 2020-11-21 21:24:18Z

1

Since you do not answer my clarification questions, supposing that the object in discussion is a `Worksheet', test the next way, please:

Sub testComboObject()
 Dim cb As DropDown, cb1 As MSForms.ComboBox, sh As Worksheet, i As Long
 
 Set sh = ActiveSheet
 i = 1
'In case of a ActiveX ComboBox:
 Set cb1 = sh.OLEObjects("ComboBox" & i).Object
 cb1.Clear
 cb1.AddItem "Test1"
 cb1.ListIndex = 0
 
 'In case of a sheet Form ComboBox (DropDown):
 Set cb = sh.Shapes("Combo" & i).OLEFormat.Object
  cb.RemoveAllItems
  cb.AddItem "Test1"
  cb.ListIndex = 1
End Sub

A combo box does not receive a value like a Text Box. You should add items and then set the combo ListIndex property to make it having a value...

answered Nov 21, 2020 at 21:24

FaneDuru

42.3k4 gold badges22 silver badges30 bronze badges

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

3 Comments

Lucas Over a year ago

worked perfectly. Thanks a lot for the help. As per my understanding the .controls will just work if i use in in a userform but not in the worksheet, right ?

Lucas Over a year ago

Hello, its me again (Like Adele) I have an array that I have created previously called ArrProd if I use the command me.combobox1.list=ArrProd it works nice. but if i use cbl.List=Arrprod I receive the error 424 message. what am I doing wrong now ?

FaneDuru Over a year ago

@Lucas: If I remember well, Error 424 means "Object required". So, I think that you forgot to properly define the cbl object. Without seeing the context of your code I cannot say more about the issue... If your code refers a User form or a Worksheet, Me means the parent object of the combo (the Form or the Sheet). If you use only a variable not previously defined, VBA does not know what kind of object it is and if it has a List property or not...

Lucas · Accepted Answer · 2020-11-23 21:01:54Z

i've create the following function to mount an array with a list of products

Public Function MontArrProdutos()
Dim shtBD_PROD As Worksheet


Set shtBD_PROD = Sheets("BD_PROD")
lastrow = FindLastRow(shtBD_PROD, "B")
arrProd = shtBD_PROD.Range("A2:B" & lastrow).Value



End Function

After that I made a Little small modification to your code

Sub testComboObject()

 Dim cb As DropDown, cb1 As MSForms.ComboBox, sh As Worksheet, i As Long
 MontArrProdutos ' CALL THE FUNCTION TO MOUNT THE ARRAY OF PRODUCTS
 Set sh = ActiveSheet
 i = 1
'In case of a ActiveX ComboBox:
 Set cb1 = sh.OLEObjects("ComboBox" & i).Object
 'cb1.Clear commented this in order to make minor changes to code
 'cb1.AddItem "Test1" commented this in order to make minor changes to code
 'cb1.ListIndex = 0 commented this in order to make minor changes to code
cbl.List= arrProd ' inserted this line only to populate the combobox1

end sub

all of this is inside a worksheet, i'm not using any form in project

Collectives™ on Stack Overflow

VBA Excel cannot use Controls library

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related