ComboBox Erratic Behavior

When I put the code below in a UserForm (VBA Editor) it works as it should but when I use an ActiveX combobox in an ActiveX worksheet where I keep the remaining control, sometimes works well and sometimes it does not

Private Sub cboSelect_Change()
'Private Sub cboSelect_Initialize()
'cboSelect.Initialize
'cboSelect.Activate
Me.cboSelect.List = WorksheetFunction.Transpose(Sheet2.Range("B8:O8"))
End Sub

The lesson I follow says to doubleclick the cboSelect control and in the code window which will open to choose “Initialize”. But since I use an ActiveX control the only option I have is to write the code manually. If I choose a Form combobox in my ActiveX form the only option I have is to assign a Macro

How can I make it work?

Very sorry Jeff, you are right the lesson says to doubleclick the userform and choose view code and from the top right hand corner click the drop down arrow for the events and select "Initialize". Then to add this code

Private Sub UserForm_Initialize()
Me.cboSelect.List=WorksheetFunction.Transpose(Sheet2.Range("B8:o8"))
End Sub

But by having ActiveX form with all my controls I dont have UserForm to doubleclick. So where and how to put this code for the combobox?

The Range("B8:O8")) in the combobox is for the 14 headers I have in my db (LastName, FirstName, etc.). When I click on the arrow in the combobox a list drops down with the names and I click anyone I want to do a search. Sometimes when I click the drop down arrow nothing happens I mean there is no list or the space for the list and sometimes all the names are there. But once the list is there it stays and works well until I close the file. Then if the list does not work I go to the VBA editor, delete / re write the above mentioned code a few times and then it works again. Thanks for the help

I tried this code and looks like it works fine so far

Private Sub cboSelect_DropButtonClick()
Me.cboSelect.List = WorksheetFunction.Transpose(Sheet2.Range("B8:O8"))
End Sub

The Initialize Sub associates your combobox with a list of data used to populate it. It is called the first time that the userform is displayed, and will "stick" for the rest of the life of the session.

The DropButtonClick Sub says what's going to happen every time the user clicks on the dropdown. You don't need to keep resetting the list range every time the user clicks--it only needs to be done once, in the Initialize sub. Doing it here doesn't hurt anything but it doesn't do anything useful, either.

Very good info StringJazzer thank you, now I know the difference and looks like that I must find a way to Initialize it. But since this is an ActiveX control how I can do that?

The code for Initialize you have already done and it's fine

The code for DropButtonClick is unnecessary and can be removed.

When I REM out the DropButtonClick the comboSelect button is not working (I don't see the list anymore)

It is really difficult to help with this just getting bits and pieces here and there.

Here is a sample file with your code that works. There is no Sub for DropButtonClick. See how this compares to yours. To get further help you're going to have to attach your file.

Thanks for your time. I don't have much experience with VBA but I would say your file is with Form control and mine is with ActiveX. I had downloaded my workbook on this thread

http://www.excelforum.com/showthread...94#post4071694

Please look in my post #5 on the above link to get the download. The combobox is at the top left of the form under the label Select Field

the lesson says to doubleclick the userform

It sounds like your assignment is to create a UserForm. But you do not have a UserForm in this file. Instead you are using a worksheet to create a form. That's certainly a valid way to get information from a user, but that's not what your assignment says to do.

The Initialize event is associated exclusively with UserForm. That's why it doesn't work when you try to put it in a worksheet.

My example uses ActiveX controls on a UserForm. The UserForm is the thing that pops up when you click the button. All controls in a UserForm are ActiveX.

I did complete the lesson with a UserForm but then I got more adventurous and decided to make my own ActiveX form to see the difference. Now that you pointed out, I agree the Initialize was for a UserForm. This is a hobby project to learn VBA and your help is appreciated

Your code reloads the combobox every time the user touches it. In purely practical terms, it works and doesn't do anything wrong. But from a coding practice standpoint, you are doing something repeatedly that only needs to be done once. This could be done, for example, by using the Workbook_Open event in the workbook, which will load the combobox once when the workbook is first opened. In some cases, this would make a significant difference in performance and response time.

Good to know StringJazzer, when my knowledge improves I will try the Workbook_Open event. Thank you