I have an error during initialization and I do not get why this is not working:
Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = Worksheets(1)
ws.ComboC15.AddItem "Technology"
end sub
It is very weird, the code should be correct. I have checked the combo name and it is "ComboC15".
In fact, this is the sub:
Private Sub ComboC15_Change()
Ps: I also checked the sheet and it is the first sheet where I have the Combo
The Worksheet object in VBA doesn't have a property or method called ComboC15, as this is entirely your own invention. However, the individual worksheet object in your workbook, i.e. the sheet itself as a physical sheet and not as a VBA sheet, knows all about ComboC15 since it's been dropped on it by you. Therefore, you need to access the worksheet object (notice the little w) and not the Worksheet object (notice the big W). If it's confusing to read, imagine how confusing it is for me to try to explain this...
To get access to the worksheet Object, you can do any of the following:
' Assuming "Sheet1" is the code name of the object. You can find this code name
' in the VBA editor. In the "Project Explorer" window, look under Microsoft
' Excel Objects. Your sheets are listed there in the form (for a blank, new
' workbook) "Sheet1 (Sheet1)". The bit *outside* the brackets is the code name.
Sheet1.ComboC15.AddItem "Technology"
' You can even call it directly from the "Worksheet" object by using the sheet
' index.
Worksheets(1).ComboC15.AddItem "Technology"
However, if you want to create a variable with it so you don't have to copy/paste the same thing over and over, declare it as an Object and not as a Worksheet. So do this:
Private Sub Workbook_Open()
Dim ws As Object ' Declare ws as "Object"!
Set ws = Worksheets(1)
ws.ComboC15.AddItem "Technology"
End Sub
The following SO Q&A explains a bit more about the different kind of sheet names:
and I hope this article might give you a better insight on how to reference different kinds of objects in VBA.
