0

I'm very new to DBA so probably it's a banal mistake but I looked around and I did not found anything that could help me. I'm trying to populate a combobox dynamically using the content of a column (column "A" in this specific case) using a macro linked to a button. If the analyzed cells are empty everything goes smoothly and the message "done!" appears, but if there is any data in the cells I get the error "424 object required access". I don't know if it would help: I took the code from this youtube video https://www.youtube.com/watch?v=x8O59GtatH8 and adapted it (just removed the listox) the complete code is at 5.35

I'm probably misunderstanding something very basic. I am guessing the declaration of the combobox.

Sub prova_stessa_scheda()

row_review = 1

    Dim TheSheet As Worksheet
    Set TheSheet = Sheets("Listino_prezzi")




   Do

   DoEvents

   row_review = row_revieew + 1

   item_in_review = TheSheet.Range("A" & row_review)

    If Len(item_in_review) > 0 Then ComboProva_Change.AddItem (item_in_review) 'this is the command that gives the error

    Loop Until item_in_review = ""

    MsgBox "Done!"




End Sub

I expected that when the macro gets triggered the combobox gets filled with the value written in the cells of column "A" instead I got the error 424.

Darren Bartrup-Cook
  • 16,135
  • 1
  • 18
  • 37
SingingRaven
  • 25
  • 6
  • Where does your code live - is it code of a form or a module? Is the name of the Combobox really `ComboProva_Change`? Put a statement `Option Explicit` at the top of your code - if you have a misspelling of the name, the compiler will tell you. – FunThomas Dec 20 '18 at 09:10
  • Sorry I forgot to specify it, the code lives in a module. I'm feeling dumb now, the name of the Combobox is CombProva (the name that you see if you right-click on the combobox and go on properties right?) I took the wrong name from another code. However I if I had Option Explicit I got the error "Variable Not Defined Compiler Error" the line "Sub prova_stessa_scheda()" get highlighted. thanks for the help. – SingingRaven Dec 20 '18 at 09:47

1 Answers1

0

If you put your code into the Worksheet module of the sheet where the combobox is placed, VBA is assuming that you want to access the element CombProva of that sheet (this is what is done in the video, only with different names).

However, if you put your code into a regular module, VBA does not know what CombProva is. You have to tell VBA that you want to access it from a specific sheet. There are several ways to do so:

(1) Use

With ThisWorkbook.Sheets("Listino_prezzi")
   .ComboProva.AddItem (item_in_review)
End With

Note that the following code will throw an compiler error. This is because TheSheet is of type Worksheet, so it could be any worksheet, and a worksheet does not have anything named ComboProva.

Dim TheSheet As Worksheet
Set TheSheet = Sheets("Listino_prezzi")
With TheSheet 
   .ComboProva.AddItem (item_in_review)     ' <-- Compiler error
End With

(2) You can access the sheet also by its CodeName. If you look to the video: The sheet itself was renamed to Admin Site, but the CodeName is still Sheet1 (the CodeName can only be changed in the VBA editor in the Property-window). So you can write 

With Sheet1
   .ComboProva.AddItem (item_in_review)
End With

(3) You can access the combobox by name from the Shapes-collection of the sheet (basically everything that is put on a sheet but not within an cell is a Shape). However, as you are dealing with ActiveX-controls, this is a little bit ugly.

Dim sh as Shape
Set sh = TheSheet.Shapes("ComboProva")
With sh.DrawingObject.Object
    Call .AddItem("Variante x")
End With
FunThomas
  • 11,970
  • 1
  • 13
  • 30
  • Thanks for the help, I tried all 3 methods more time but it doesn't work yet, it gave me the same error (of course it must be something very silly that I do wrong). Where do you have to put the code that ou gave me or what I need to substitute? I double checked the name of the combobox, so that's not the problem. Thank you very much for the help an patince. – SingingRaven Dec 20 '18 at 13:17
  • Can it be that you inserted a Form Control rather than a Active X Control? See https://stackoverflow.com/q/15455179/7599798 - or really detailed https://stackoverflow.com/a/50144021/7599798 – FunThomas Dec 20 '18 at 13:55
  • Yes! That was the problem! I know that was something silly! Thanks for the help and knolage that you gave me! – SingingRaven Dec 20 '18 at 14:15